KnowledgeBase 00034: Making Applications Work Across Timezones

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.

Epoch Values

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.

Conversion Codes

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.