Dates, Times and Epoch Values

Dates, Times and Epoch Values

Top  Previous  Next

 

Dates

 

Dates are usually stored as a number of days since 31 December 1967 (day zero). All dates after that point are represented by positive numbers. All dates before that point are represented by negative numbers. This form of date is used by all multivalue databases and means that there was no issue with the millennium (day 11689). The multivalue world had its own date crisis on 18 May 1995 (day 10000) when developers discovered that they had stored the date as four characters of a composite record id or were sorting dates as character strings rather than numbers such that 17 May 1985 (day 9999) came after 18 May 1995. This potential problem still applies to any application that handles historic dates but the advantages of working with a simple day number internally far outweigh any disadvantages.

 

A user can see the current date in its internal or external form by using the DATE command.  This command can also translate an arbitrary internal or external form date to its counterpart.

 

A programmer can access the current date using the DATE() function or the @DATE variable. The difference between the two is that DATE() returns the internal form of the date when the function is executed whereas @DATE is the internal form of the date when the currently executing command began.

 

The D conversion code can be used by applications to transform dates between their internal and external form. This conversion code can decode many elements from an internal date including day of the month, day of the week as a number (Monday = 1, Sunday  = 7), day of the week as a word, Julian date (days into year), month as a word or a number, ISO week number, quarter of the year, and year. The day of the week as a number can also be calculated simply by taking the remainder from dividing the day number by seven.

 

 

Times

 

Times are usually stored as a number of seconds since midnight (0 to 86399).

 

A user can see the current time in its internal or external form by using the TIME command.  This command can also translate an arbitrary internal or external form time to its counterpart.

 

A programmer can access the current time using the TIME() function or the @TIME variable. As described above for dates, the difference between the two is that TIME() returns the internal form of the time when the function is executed whereas @TIME is the internal form of the time when the currently executing command began.

 

The MT conversion code can be used by applications to transform times between their internal and external form.

 

 

Composite Dates and Times

 

Sometimes, an application developer may find it useful to have a composite item that represents both the date and time. One way to do this is to store it as the day number * 86400 plus the time, a value that equates to seconds since midnight between 30 and 31 December 1967. QM provides a function, SYSTEM(1005), to return the current time in this form.

 

The QMBasic TIMEDATE() function returns a combination of the current date and time in text format.

 

 

Epoch Values

 

Today's business applications are frequently used by networked users who may span time zones. The date and time functions mentioned above all return data appropriate to the time zone of the QM process for the user running the application. This time zone is initially determined by the setting of the TZ environment variable at the operating system level on entry to QM but can be modified using the TIMEZONE private configuration parameter or the QMBasic SET.TIMEZONE statement. The time zone can differ from one user  to another, thus, if two users in different time zones evaluate the TIME() function simultaneously, the returned value will be different. In some cases, storing a date or time that is appropriate only to the user who saved it may be meaningful but it is more likely that we need a way to store dates and times in a time zone independent manner that represents a moment in time. A value stored in this form can then be converted to the local time zone of the user later, giving a local representation of that moment in time such that a single stored time value may be represented differently on the screen for different users.

 

The underlying principle of this extension to the multivalue date and time handling is that we store the date/time as an epoch value. This is the number of seconds since the start of January 1970 UTC (closely related to GMT) and is a concept that is widely used in other computer systems. Because epoch values are independent of the user's time zone, the same data can be used unambiguously by all users of the system, regardless of where they are in the world.

 

In some 32-bit operating systems, epoch values are defined to be 32 bit signed integers. This leads to a limitation that they can only represent times between 13 December 1901 and 19 January 2038. This is a widely recognised issue, potentially more widespread than the "millennium bug", that will require changes to operating systems and run time libraries.

 

More seriously, Windows support for time zones at the application level is very poor. Although QM's epoch handling functions will operate to some extent, the time zone name must include the offset from GMT (which implies that it cannot automatically resolve daylight saving time for an arbitrary date) and Windows is incapable of processing dates before 1 January 1970 as epoch values.

 

QMBasic provides several functions to work with epoch values:

epoch = EPOCH()Returns the current epoch value.
date = MVDATE(epoch)Returns the date part of an epoch value as a day number relative to 31 December 1967 in the user's time zone.
time = MVTIME(epoch)Returns the time part of an epoch value as a number of seconds since midnight in the user's time zone.
time.string = MVDATE.TIME(epoch)Returns the date and time values as a string with an underscore between the two elements.
epoch = MVEPOCH(time.string)Returns the epoch value for the given time string in the form returned by MVDATE.TIME().
epoch = MVEPOCH(date, time)Returns the epoch value for the given date and time values.

 

The E conversion code provides the combined facilities of the D and MT conversion codes plus several features that only apply to epoch values.

 

 

Linux/Mac Time Zones

 

In Linux and Mac systems, time zone names are taken from the Olson Database and are constructed from an area and location pair (e.g. America/New_York). In some cases, the location element is further divided (e.g. America/Indiana/Indianapolis). Names such as GMT, EST, CET, etc are also recognised.

 

For a detailed discussion of time zones, see http://en.wikipedia.org/wiki/Zoneinfo and http://en.wikipedia.org/wiki/List_of_zoneinfo_time_zones.

 

The Olson Database is part of the operating system, not QM. Updating this database is automatic on some platforms (e.g. Mac) but may require actions by the system administrator on some other platforms.

 

 

AIX Time Zones

 

AIX provides partial support for time zones. The default time zone is taken from the TZ environment variable. Any time zone set using the CONFIG command or the QMBasic SET.TIMEZONE statement must be in the format required by AIX (e.g. EST5 or EST5EDT). The Z element of the E conversion code (time zone name) will return a null string.

 

 

Windows Time Zones

 

Windows support for time zones is very limited. The time zone name is of the form

z{+ | -}d{s}

where

 

zis a three character string representing the name of the current time zone. For example, the string “CET” could be used to represent Central European time but Windows make no use of the actual name.

 

dis an optionally signed item with one or two digits specifying the local time zone’s difference from GMT in hours. Positive numbers adjust westward from GMT and negative numbers adjust eastward from GMT. (e.g. EST5, PST+8, CET-1).

 

sis an optional three character field that represents a name for the local time zone when daylight saving time is in effect.