Timezoneless Datetimes

Why

Some traditional relational databases keep all values of DATETIME type as combination of time and timezone data. Others treat all time values as if they're in a single timezone (which might be specified by configuration, or inferred from local environment), which may mean conversion of submitted values which include timezone information or simple omission of that information upon storage.

In RDF, incoming triples may contain literals of types like xsd:dateTime with arbitrary values matching ISO 8601, and this standard makes inclusion of timezone optional, i.e., both timezoned and timezoneless values may be included.

This mix makes it necessary to handle both "timezoned" and "timezoneless" datetimes inside one database. Virtuoso servers support this starting from version 07.20.3213.

Important: the use of timezoneless datetimes may result in subtle errors in data processing. Applications that worked fine with timezoned datetimes may work incorrectly when timezoneless datetimes are used. Such application errors may stay unnoticed during local testing and only be revealed after worldwide use.

To stay on safe side, the use of timezoneless datetimes remains blocked with databases created pre-07.20.3213, even after the server executable is upgraded, so old applications will continue to work as before. When developing new applications, please pay attention to the check-list at the end of this section.

What

As of version 07.20.3213, Virtuoso supports DATETIMEand xsd:dateTime values both with and without timezone information, i.e., both "timezoned" and "timezoneless".

How

Enabling Timezoneless Support

Different applications may require different behavior when input data contains timezoneless values.

In some cases, it is better to simply CAST all values to timezoned than to upgrade existing application code.

Virtuoso offers five different modes of support for timezoneless values. The mode is selected by setting the "TimezonelessDatetimes" parameter in [Parameters] section of virtuoso.ini. This should be set before creating the database and the set value is stored in the database.

After database is created, an attempt to change the mode by changing the setting in the virtuoso.ini, will have no effect and the virtuoso.log will contain a warning about mismatch between setting in the virtuoso.ini file and the database file.

The possible variants are:

TimezonelessDatetimes setting effect
0
(or unset)
Never use timezoneless, as it was in old databases. Always set local timezone on parsing strings if no timezone specified. An attempt to set timezoneless by calling function forget_timezone() will signal an error. Timezoneless values still may come from outside as deserializations of timezoneless DATETIME values, serialized by other database instances, but not in any other way.
1 When parsing strings, set timezoneless if ISO format tells so.
2 Set timezoneless always, exception when the parsed string contains explicit timezone or when RFC requires the use of GMT or when timezone is set by the function adjust_timezone(). This is default for new databases if TimezonelessDatetimes parameter is missing in virtuoso.ini.
3 Never use timezoneless. Always set local timezone on parsing strings if not timezone specified. An attempt to set timezoneless by calling function forget_timezone() will signal an error. Timezoneless values still may come from outside as deserializations of timezoneless DATETIME values, serialized by other database instances, but not in any other way. The difference with TimezonelessDatetimes=0 is that timezones are always printed on cast datetimes to strings etc. so timezoneless-aware clients will get unambiguous data.
4 On parsing string, set timezone to GMT if no timezone was specified. However, timezoneless can be set by calling function forget_timezone(). This mode can be convenient for global web services when real "local" timezones of specific users are not known.

For most new applications, we recommend TimezonelessDatetimes = 2, with TimezonelessDatetimes = 1 as the second-best.

Formats of Datetime Strings

Traditional SQL strings are of format "YYYY-MM-DD hh:mm:ss" with optional decimal fraction at the end and then optional tinmezone data. Depending on software, the timezone can be specified as "timezone offset", i.e., the difference with GMT in minutes, or as "timezone label", i.e., an identifier of timezone in special system dictionary that contains not only an offset in minutes but also information about daylight saving changes of the offset. Virtuoso does not support timezone labels, only numerical timezone offsets. Depending on the system, notation without timezone data at the end may mean timezoneless value or, more probably, the value in some "default" timezone, such as the server's local timezone or GMT.

ISO 8601 introduced format "YYYY-MM-DDThh:mm:ss", with "T" character between the "date" and "time" parts. It also prescribed an unambiguous difference between timezoneless and timezoned values: an absent timezone means a timezoneless value.

The timezone offset is written as "+hh:mm" or "-hh:mm", the "+00:00" is usually shortened to "Z".

Oracle Java may use 1 to 4 digits without delimiting ":"; in that case, 1 or 2 digits mean whole hours whereas 3 or 4 digits mean 1 or 2 digits of hour and two digits of minutes. For historical reasons, "-00:00" notation differs from "+00:00" and means timezoneless, not GMT datetime.

Comparison of Datetimes

ISO 8601 explicitly warns that comparison of timezoned and timezoneless datetime is not always possible. Valid timezones vary from -14:00 to +14:00; the fact that the span can exceed 24 hours may not be immediately obvious.

Nevertheless, storing rows in a database table require some unambiguous order; any order is OK as long as it does not break the rules and common sense, but it should be well-defined.

Virtuoso's ordering for a mix of timezoned and timezoneless datetimes is very simple:

  1. All timezoned datetimes are sorted in natural chronological order, based on their GMT equivalence. The value of timezone offset does not matter.
  2. All timezoneless datetimes are sorted in natural chronological order, as if in GMT.
  3. For each GMT calendar day, all timezoned datetimes are placed before all timezoneless datetimes.

Related Functions