Zulu Time and Duration Formatting with FileMaker

Occasionally, I will save bits of code in small files to reference later in my FileMaker work. This is one of those files. I have to warn you though, if you are triggered by discussions of Time Zones and Daylight Savings, you can exit here. I will discuss them in the context of FileMaker functions to be compatible with other standards and systems. With that in mind, please forgive the following preamble if you are already familiar with the concepts.

Time Zone Concepts

The concepts of time zones were formalized in Greenwich, England, where it was decided that it (Greenwich) would be the first zone, or mean, and all others “zones” would be offset from there. This really drives at the underlying concept that there are two types of time; a universal time that is the same worldwide, and the relative time that we use in different zones.

Relativity

I say “relative,” because the observed time is relative to where the sun comes up in your part of the world. Noon is when the sun is highest in the sky at 12:00 PM at relatively the same time of day.

Similarly, we have breakfast in the morning (a relative term) around when the sun comes up and dinner when the sun goes down. That way, no matter where you travel around the globe, the same relative time is observed. You don’t have to eat breakfast at 11 PM.

Coordinated Universal Time, or UTC

Since time zones were invented in Greenwich, England, they defined it as the time zone by which others are measured. That zone is known as Greenwich Mean Time, or GMT. Every other time zone is measured as offset from GMT.

Where GMT is an actual time zone, UTC (Coordinated Universal Time) is the standard that time zones are set by. UTC time never changes because of daylight savings; only certain time zones do.

Functionally, GMT and UTC are the same time, but one is a time zone. The other is the time standard.

Standard Formats for Sharing Information

It is common practice to define fields in your database for creation date and time as well as modification date and time. However, those are commonly defined to use the relative time. This can lead to unforeseen issues, such as a user in California creating a record one hour after a user in New York but will appear to have been created two hours before the user in New York. This occurs because of the local times in both those places are in different time zones.

If you have a multi-regional business or do business globally, you might want to consider this while designing your system. At a minimum, you will want to use a standard definition for everyone in your system, for example, the time on the server. It might still change for daylight savings, but at least it changes at the same time for everyone.

That can also create issues if you are considering record syncing. You’ll need to handle records from different time zones based on their modification dates, among any other business rules. Of course, you can always use the FileMaker Function Get ( CurrentTimeUTCMilliseconds ), which will return the number of seconds that have passed since 1/01/2001. It uses the universal standard for time. If you use syncing at some point, you may want to consider adding a field set to auto-enter the Get ( CurrentTimeUTCMilliseconds ) function.

Zulu Time

“Zulu” is the phonetic alphabet name used by NATO military for the letter Z, which stands for Zone, as in Time Zone. This is a standard for writing UTC time that follows a certain format, starting with the largest measure of time. This is an agreed upon format documented in ISO 8601.

You have several options. A timestamp in Zulu format would look like TZ. That is a date “YYYY-MM-DD” with the four-digit Year, two-digit month and two-digit day, “T” for “time,” followed by a time formatted as “HH:MM:SS” with hours, minutes and seconds, all followed with a “Z” to denote that it is Zulu format. This format is used quite a bit, especially in calendar applications such as Calendar on OS X or Microsoft Outlook.

Creating a Calculation in FileMaker

While FileMaker has no built-in function to automatically format a time stamp as Zulu time, we can create a calculation to format a string as text in the correct format. However, remember that due to time zones, if we have stored a relative time, we will need to get the offset from the UTC to get the correct Zulu time.

For example, the following is a calculation that outputs the current time, in Zulu format:

Let ([
    Dn = GetAsTimestamp ( Get ( CurrentTimeUTCMilliseconds )/1000 ) ;
    date_string = Year(Dn) & "-" & SerialIncrement ( "00" ; Month(Dn) ) & "-" & SerialIncrement ( "00" ; Day(Dn) ) ;
    time_string = SerialIncrement ( "00" ; Hour ( Dn ) ) & ":" & SerialIncrement ( "00" ; Minute( Dn ) ) & ":" & SerialIncrement ( "00" ; Seconds( Dn ) )
     ];
  date_string & "T" & time_string & "Z" 
)

This is made much easier with the existence of the function to get the current time in UTC milliseconds. However, that only returns the current time in UTC. If we want to get any arbitrary date in UTC, things get a little more complicated.

First, we need to calculate the offset from the UTC for the given date. For the moment, we will assume we have a calculation that indicates if daylight saving time is in effect for the given date. We will go into more detail momentarily.

The calculation to show that would look like the following:

Let ([
  Dn = GetAsDate( start_date ) ;
  date_string = Year(Dn) & "-" & SerialIncrement ( "00" ; Month(Dn) ) & "-" & SerialIncrement ( "00" ; Day(Dn) ) ;
  offset = Get ( CurrentTimestamp ) - Floor ( Get ( CurrentTimeUTCMilliseconds ) / 1000 );
  daylight = start_isdaylight * 3600; 
  Hr = GetAsTime ( start_time ) - ( offset ) - ( daylight ) ;
  time_string = SerialIncrement ( "00" ; Hour ( Hr ) ) & ":" & SerialIncrement ( "00" ; Minute( Hr ) ) & ":" & SerialIncrement ( "00" ; Seconds( Hr ) )
   ]; 
date_string & "T" & time_string & "Z"
)

You will note that we use the current timestamp to calculate the offset, which would work for wherever you run the calculation from. However, you could also just enter an offset manually if you want to figure it for a different time zone from your current location.

In fact, the free sample file you can download here does this. It allows you to change the offset manually or calculate the offset automatically.

Daylight Saving Disdain

I realize there is a lot of debate about daylight saving time (DST). However, whether I am for or against the observation of it (I am against it) does not change the fact that you have to account for it in your applications. Or, you may never have to deal with this issue. If that is the case, great!

Daylight Savings is far more difficult to deal with than time zones, which are hard enough. The rules have changed quite a lot over time, so a date in the past may or may not have the same rules applied as one today.

For example, legislation was passed that changed when we here in the US observe daylight saving time, starting in the year 2007. Consequently, until computers were updated with the newer time zone changes in the internal databases, they would not be able to show what time it was, based on the time zone they were configured for. This is one of many reasons why you want to keep your OS up to date.

Assuming you primarily work with dates from the last ten years or so, and in time zones generally in the United States that observe daylight savings, we can calculate if daylight saving is in effect or not with a single calculation. We can then use that in our calculation to format a timestamp in Zulu time. If you need to go back further than that or are in a different location that observes different rules, you may need to modify the calculations.

Zulu Duration

There is another standard in the Zulu format that indicates the duration of time that has passed between two points in time. This is used a lot as well as a standard of information exchange, especially in calendaring programs.

Unlike formatting a single time, a duration of time should not include any daylight saving or time zone business. Fortunately, this is the way that calculating time works in FileMaker. The math is based on the underlying application framework’s Epoch reference date, which is the number of seconds that have passed since January 1, 2001.

For this example, we need a second date and time to compare. It then gets tricky to calculate to support all options, but the general format looks like this:

P3D1H15M30S

Which is to say, a Period of 3 Days, 1 Hour, 15 Minutes and 30 Seconds in the above example.

This can be expanded on to meet your requirements if you to show a unit greater than a day, which would be months, weeks or years. The calculation in the sample supports increments of days, hours, minutes and seconds. Feel free to download and examine to see it working.

Download the Sample File

You can get the sample file that demonstrates these functions and calculations here:

References

• Get(CurrentTimeUTCMilliseconds)
• ISO 8601
• Time Zone Database