Dates with InterSystems

Do not let the title of this article confuse you; we are not planning to take the InterSystems staff out to a fine Italian restaurant. Instead, this article will cover the principles of working with date and time data types in IRIS. When we use these data types, we should be aware of three different conversion issues:

1. Converting between internal and ODBC formats.
2. Converting between local time, UTC, and Posix time.
3. Converting to and from various date display formats.

We will start by clarifying the definitions of internal, UTC, and Posix time. If you define a property within a class as a %Date or %Time data type, it will not take long to realize that these data types are not stored in the ODBC standard way. They look like integers, where the %Time will be the number of seconds after midnight, and the date is the number of days since December 31, 1840. It comes from MUMPS. When it was created, one of its inventors heard about a Civil War veteran who was 121 years old. Since it was the 1960s, it was decided that the first date should be in the early 1840s. That would guarantee that all birthdays could be represented as positive integers. Additionally, certain algorithms worked the best when every fourth year was a leap year. That is why day one is January 1, 1841, and you can sometimes see null dates displayed as December 31, 1840.

Coordinated Universal Time, or UTC, is effectively a successor to Greenwich Mean Time (GMT). It is based on a weighted average of hundreds of highly accurate atomic clocks worldwide, making it more precise than GMT. The abbreviation UTC is a compromise between English and French speakers. In English, the correct abbreviation would be CUT, whereas in French, it would be TUC. UTC was chosen as the middle ground to enable the usage of the same term in all languages. Besides, it matches the abbreviations utilized for variants of universal time, UT1, UT2, etc.

Posix time is also known as Epoch time or Unix time. It shows the number of seconds that have passed since January 1, 1970. Posix time does not account for leap seconds. That is why when a positive leap second is declared, one second of Posix time is repeated. A negative leap second has never been claimed, but if that ever happened there would be a small range of Unix time slots that did not refer to any specific moment in real time.

Because both time models mentioned above are defined as a number of seconds since a fixed point in time, and, perhaps, because it is not a useful concept in computing, neither UTC nor Posix time are adjusted for daylight savings time.

We will begin with converting between internal and external formats. Two system variables are useful in obtaining these pieces of information: $HOROLOG, which can be shortened to $H, and $ZTIMESTAMP, sometimes abbreviated as $ZTS. $H contains two numbers separated by a comma. The first one is the current local date, and the second one is the current local time. $ZTS is formatted similarly, except that the second number has seven decimal places, and it holds the UTC date and time instead of the local ones. We could define a class with the following properties:

///Will contain the local date.
Property LocalDate As %Date [ InitialExpression = {$P($H,",",1)} ];
/// Will contain the UTC date.
Property UTCDate As %Date [ InitialExpression = {$P($ZTS,",",1)} ];
/// Will contain the local time.
Property LocalTime As %Time [ InitialExpression = {$P($H,",",2)} ];
/// Will contain the UTC time.
Property UTCTime As %Time [ InitialExpression = {$P($ZTS,",",2)} ];
/// Will contain Posix timestamp
Property PosixStamp As %PosixTime [ InitialExpression = {##class(%PosixTime).CurrentTimeStamp()} ];
/// Will contain the local timestamp.
Property LocalTimeStamp As %TimeStamp [ InitialExpression = {##class(%UTC).NowLocal()} ];
/// Will contain the UTC timestamp.
Property UTCTimeStamp As %TimeStamp [ InitialExpression = {##class(%UTC).NowUTC()} ];

If we create an instance of this class and save it, it will contain the dates and times given when that instance was made. If we query this table in logical mode, we will see the underlying numbers.

There are a few ways to make this data human-readable. First, we could switch the query mode from logical to ODBC. In the System Management Portal, this action is controlled by a drop-down box. When we are writing ObjectScript code and querying with a %SQL.Statement, we can set the %SelectMode property of that object to 1 for ODBC mode as follows:

set stmt = ##class(%SQL.Statement).%New()
set stmt.%SelectMode = 1

We can also use the %ODBCOUT SQL function. You can apply the following query to give us the data in ODBC standard format:

SELECT ID, %ODBCOUT(LocalDate) AS LocalDate, %ODBCOUT(UTCDate) AS UTCDate, %ODBCOUT(LocalTime) AS LocalTime, %ODBCOUT(UTCTime) AS UTCTime, %ODBCOUT(PosixStamp) As PosixStamp from SQLUser.DateArticle

There is also a %ODBCIN function for inserting ODBC format data into your table. The following SQL will produce a record with the same values as above:

INSERT INTO SQLUser.DateArticle(LocalDate,UTCDate,LocalTime,UTCTime,PosixStamp) VALUES(%ODBCIN('2024-08-20'),%ODBCIN('2024-08-20'),%ODBCIN('13:50:37'),%ODBCIN('18:50:37.3049621'),%ODBCIN('2024-08-20 13:50:37.304954'))

Unlike the above-mentioned data types, %TimeStamp follows ODBC standards. It means that if we want to create a timestamp, we should use different functions or convert the data from the HOROLOG format to ODBC. Fortunately for ObjectScript programmers, there are many useful methods to choose from stored in the %Library.UTC class already. For example, the following line of code will convert the current local date and time, or whatever other HOROLOG formatted data you give it, to a timestamp:

set mytimestamp = ##class(%UTC).ConvertHorologToTimeStamp($H)

Of course, if we are trying to obtain the current timestamp, we can also add the following properties to our class for the local and UTC timestamp respectively::

/// Will contain the local timestamp.
Property LocalTimeStamp As %TimeStamp [ InitialExpression = {##class(%UTC).NowLocal()} ];
/// Will contain the UTC timestamp.
Property UTCTimeStamp As %TimeStamp [ InitialExpression = {##class(%UTC).NowUTC()} ];

There are even functions for converting between UTC and local timestamps. They can be a major headache saver when dealing with various time zones and daylight savings time:

set utctimestamp = ##class(%UTC).ConvertLocaltoUTC(##class(%UTC).NowLocal())
set localtimestamp = ##class(%UTC).ConvertUTCtoLocal(##class(%UTC).NowUTC())

For demonstration purposes, I have used the current time stamps. However, you can give these functions any timestamp you wish, and they will convert accordingly.

Those of us who do a lot of integration projects, sometimes are required to convert a timestamp to XSD format. For this purpose, the %TimeStamp class has a LogicalToXSD function built into it. If we use the following line:

set xsdTimeStamp = ##class(%TimeStamp).LogicalToXSD(..LocalTimeStamp)

We will get a string formatted as YYYY-MM-DDTHH:MM:SS.SSZ, e.g., 2024-07-18T15:14:20.45Z.

Unfortunately for SQL users, these functions do not exist in SQL, but we can fix that by adding a couple of class methods to our class using the SqlProc keyword:

ClassMethod ConvertLocaltoUTC(mystamp As %TimeStamp) As %TimeStamp [ SqlProc ]
{
    return ##class(%UTC).ConvertLocaltoUTC(mystamp)
}
ClassMethod ConvertUTCtoLocal(mystamp As %TimeStamp) As %TimeStamp [ SqlProc ]
{
    return ##class(%UTC).ConvertUTCtoLocal(mystamp)
}

Then we can utilize SQL similar to the following to make the conversion:

SELECT DateArticle_ConvertLocaltoUTC(LocalTimeStamp), DateArticle_ConvertUtctoLocal(UTCTimeStamp), DateArticle_ConverttoXSD(LocalTimeStamp) FROM DateArticle

Please note that I have named my class User.DateArticle. If your class name is different, it will replace "DateArticle" in the query mentioned above. Also remember that the XSD conversion function does not care whether you give it a UTC or local timestamp since it is just converting the output format of the timestamp, not its value.

DATEADD and DATEDIFF are two other important functions to know when dealing with dates. They are typically used to add and subtract increments to and from dates or find a difference between two dates. Both of them take an argument specifying what units to work with first, and then they receive two dates. For instance, DATEADD(minute,30,GETUTCDATE()) would give you the UTC timestamp 30 minutes from now whereas DATEADD(hour,30,GETUTCDATE()) will deliver the timestamp 30 hours from now. If we wanted to go backward, we would still employ DATEADD, but with a negative number. DATEADD(minute,-30,GETUTCDATE()) would give you the time 30 minutes ago. DATEDIFF is used to find the difference between two dates. You can calculate your current offset from UTC by utilizing the following line:

SELECT DATEDIFF(minute,GETUTCDATE(),GETDATE())

It will give you your offset in minutes. In most time zones, hours would suffice. However, a few time zones have a thirty or forty-five-minute increment from the neighboring time zones. Therefore, you should be careful when using it since your offset might change with daylight saving time.

In ObjectScript, we can also operate the $ZDATETIME function, which can be abbreviated to $ZDT, to show our timestamps in various formats, e.g., some international formats and common data formats. $ZDT converts HOROLOG formatted timestamps to those formats. The reverse of this function is $ZDATETIMEH, which can be abbreviated $ZDTH. It takes a lot of different formats of timestamps and converts them to IRIS’s internal HOROLOG format. $ZDT alone is a powerful function for converting internal data to external formats. If we use both these functions together, we can convert from any of the available formats into any of the other available formats.

Both $ZDT and $ZDTH take a dformat argument specifying the date format and a tformat argument specifying the time format. It is worth taking the time to read those lists and see what formats are available. For example, dformat 4 is a European numeric format, and tformat 3 is a twelve-hour clock format. Therefore I can output the current local time in that format by applying the following line:

write $ZDT($H,4,2)

It will show me 24/07/2024 02:55:46PM as of right now. The separator character for the date is taken from your locale settings, so if your locale defines that separator as a . instead of a / you will see that in the output. If I were to start with that as my input and wanted to convert that time to the internal HOROLOG format, I would apply the next line:

write $ZDTH(“24/07/2024 02:55:46PM”,4,2)

It will write out 67045,53746. Now, let’s say I have the timestamp in the European twelve-hour clock format. However, I want to display it in an American format with a 24-hour clock. I can operate both functions at once to achieve that result as demonstrated below:

write $ZDT($ZDTH(“24/07/2024 02:55:46PM”,4,2),1,1)

It will write out 07/24/2024 14:55:46. It is also worth pointing out that if you do not specify a dformat or a tformat, the conversion will use the date or time format specified by your locale.

Speaking of locale, those of us who do business internationally may occasionally want to work with different time zones. That is when the special $ZTIMEZONE (or $ZTZ) variable comes into play. This variable contains the current offset from GMT in minutes. You can also change it to set the time zone for a process. For instance, I live in the US Central time, so my offset is 360. If I want times to be expressed in Eastern time for one of my customers, I can set $ZTIMEZONE = 300 to make the process express dates in that time zone.

As previously mentioned, UTC and Posix both count from a specific point in time. Just as they are not affected by Daylight Saving Time, they are also not affected by time zones. This means that things that rely on them, such as $ZTIMESTAMP, are not influenced by $ZTIMEZONE. Functions that refer to your local time, however, are. For example, the following sequence of commands will write out the current date and time in my local time zone (Central time), and then in Eastern time:

write $ZDATETIME($H,3)
set $ZTIMEZONE = 300
write $ZDATETIME($H,3)

However, replacing $H above with $ZTS would just show the same time twice.

If you wish to convert between UTC and local time while taking into account any changes you have made to $ZTIMEZONE in your current process, there are additional methods to help you with that. You can use them as follows:

WRITE $SYSTEM.Util.UTCtoLocalWithZTIMEZONE($ZTIMESTAMP)
WRITE $SYSTEM.Util.LocalWithZTIMEZONEtoUTC($H)

You could also utilize the methods from the %UTC class that we covered above.

With these tools, you should be able to do any kind of time conversion you need except converting the current local time to five o’clock!