In our last post of this series, we looked at how number and date searches work with the FileMaker search engine. In this post, we will consider searches in fields defined as time or timestamp.

Time fields

A time field can store a time of day or a duration. Behind the scenes, times are treated as the number of seconds since 0:00:00. This greatly aids their use in calculations such as adding a period to a start time, or determining the difference between two times.

Time Maths

When subtracting one time from another, the result will be given in seconds. To convert this to minutes, use calculations like result / 60 or Div ( result; 60 ).

Time data is entered with a separator character between the hours, minutes and seconds. This is usually a semi-colon (:) but is dependent on the file system formats.

If you enter a simple number into a time field, FileMaker Pro will see that as a number of hours e.g. 3 is 3 hours or 3am, 16 is 16 hours or 4pm. If you need to enter one minute, you should enter 0:01 (or 0:1).

INTERESTING FACT

If you enter an invalid number of minutes or seconds (over 59), FileMaker Pro will accept the entry and display the time correctly. For example, if you enter 1:99:99, FileMaker Pro will display 2:40:39. The entry for 99 seconds has become 1 minute 39 sec; the entry for 99 minutes has become 1 hour 39 minutes. All totalled with the 1 hour gives 2 hours, 40 minutes, 39 seconds.

Time fields can be formatted to display the time entered in a wide range of formats.

Time searches

When searching time fields, simple numbers are considered to be a number of hours. FileMaker Pro constructs a wildcard search. If you enter 4 in a time field of a find request, FileMaker Pro will search for 4:*:*. So the search will return all records with a time of 4 hours and any number of minutes. You can also search for times within a specific hour of the day such as 5pm – this finds all times between 5:00:00pm and 5:59:59.999999pm.

Time fields will accept range searches using the ellipsis (…). So you could search for all records between 4:15 and 4:30 using 4:15…4:30.

Time fields also accept the comparison operators:  >  <  ≥  ≤  as would be expected.

A time search will not accept # for a missing digit (e.g. 4:5#).

Timestamp fields

Timestamp fields store a date and a time combined. When entering data, you must enter both a valid date and a valid time (in that order). Timestamps are usually populated with auto-entered values (field options or a script step to Set Field).

If used in calculations, FileMaker Pro converts the timestamp to the number of seconds since 01/01/0001 0:00:00. As for times, simple maths can be performed on timestamps. Just remember to convert results from seconds into something more useful.

Timestamp fields act like a combination of a date and a time field.

Timestamp searches

Timestamps can be searched for dates, times or both. All the rules for date and time fields apply.

NEXT UP

In the next post, we will look at searches in calculation and summary fields.