Date and Time functions in KCML

KCML generally uses Julian dates to represent a date in a database as an integer day number in a standardized epoch. Todays Julian date is available with the #DATE function. To represent the date in a string you can use CONVERT DATE to get an ISO-8601 date in YYYY-MM-DD format. It is also available as a YYYYMMDD with the $TODAY function. The deprecated DATE function returned todays date as YYMMDD. These date functions all use local time.

Times are represented by a count of the number of seconds since midnight local time. The #TIME function will return the time this way. To get the same infomation in a HHMMSS string the TIME function can be used or CONVERT TIME can be used to convert #TIME to an ISO-8601 time string with the format of HH:MM:SS. These times are all local times.

Time stamps are best implemented using the $TIMESTAMP function which returns the current date and time in terms of the number of milliseconds since 0000 GMT year 0000 (one day before 1 January 0001). Note that this is not a local time and is with respect to GMT on operating systems that implement timezones. This is an unsigned binary integer returned as a six byte string.

Both Unix and NT understand the concept of timezones and can be setup to use GMT time internally with local time available by specifying the timezone. This means that system times such as timestamps on files are held in GMT. Unix systems can use the TZ environment variable to do this per process, usually setting it in kconf.xml or the .profile login script if different from the system default. On NT the timezone can be set per user with a profile. Windows 95 and 98 do not distinguish internally between GMT and local time and always operate on local time.

The difference in minutes between local time in the timezone and GMT is stored in bytes 43 and 44 of $MACHINE as a two byte signed value. This will be negative for timezones east of Greenwich Prime Meridian. Any daylight saving time bias will be negative e.g. in the UK, where TZ=GMT0BST, the value of this $MACHINE field will be -60 in the summer, reflecting the one hour BST bias, and in winter it will be zero. In the eastern part of the US and Canada, where TZ=EST5EDT, in summer it will be 240 and in winter 300. The timezone offset in $MACHINE is recalculated each time the TIME, DATE, $TODAY, or $TIME functions are executed. If KCML is running across a change of daylight saving time then this will be taken into account only if TIME, DATE, $TODAY, or $TIME is first called.

TZoffset = VAL(STR($MACHINE,43),-2)

Windows KCML

Prior to KCML 7.16.00.23073 Windows KCML ignore the TZ environment variable and local time is always the local time set for the server and not that of any client. From this version the TZ environment variable is now supported. This uses the function in the Microsoft C library which implements daylight savings according to US standards and not real local policies (https://msdn.microsoft.com/en-us/library/90s5c885(v=vs.100).aspx). For this reason it is recommended not to rely on daylight savings rules but to use just the local setting. i.e. instead of TZ="CET-1CEST" use TZ="CET-1" or TZ="CEST-2" depending on whether daylight savings is in force.

If the TZ environment variable is not specified then KCML will work as before and use the local time for the server.