|Powered by QM on a Rpi server|
KnowledgeBase 00034: Making Applications Work Across Timezones
This article was originally published as a Tip of the Week.
In today's business world, applications frequently have users spread across multiple timezones. If data records include timestamp information, this can lead to confusion and errors. The standard multivalue DATE() and TIME() functions return data appropriate to the time zone setting of the user executing the function (often actually the timezone of the server). Storing such values in the database is meaningless if they will be accessed by users in another timezone.
QM provides the ability to store references to a point in time in an unambiguous manner. This is achieved by storing it as an epoch value which is simply the number of seconds from a fixed reference point. Unlike multivalue style dates and times which are relative to a point in the user's own timezone, epoch values are relative to a fixed point that is not affected by the timezone of the user.
The current epoch value can be obtained in an application by use of the EPOCH() function. The epoch value for a specific point in time expressed in the local timezone of a user can be obtained using the MVEPOCH() function. By storing the time reference in this timezone independent form, it can be accessed unambiguously by all users. A simple appointment booking system no longer has the opportunity for confusion where the person making the appointment is in a different timezone to the place at which the appointment will take place.
Applications that use epoch values often need to display these to the user in their own local timezone. The E conversion code provides all the features of both the D and MT conversions combined into a single operation, allowing display of the date and/or time in the local timezone of the user or any other timezone requested by the application.
Daylight Saving Time
The switch into or out of daylight saving time has always posed a problem to applications that work with times. When the clocks go forward, there is an apparent missing hour in the early hours of the morning. An application that needs to calculate the difference between two times across this change will give a result that is actually one hour too long.
Conversely, when the clocks go back, there is a period of one hour for which the same times appear to happen again. An application calculating the difference between two times may find that the end point is before the start point.
Using epoch values avoids this problem completely. The epoch value continues counting seconds in a linear manner with no discontinuities when the clocks change. Any issues of daylight saving time are handled in the conversion of an epoch value to a local time.
Operating System Support
The support provided for epoch values by different operating systems and compiler libraries is somewhat variable. Linux and Mac systems do the job well by using the Olson database of timezone information that is periodically updated automatically.
Windows and AIX systems work fine when the epoch value is to be converted to or from the user's timezone but present some minor difficulties when using any other timezone.