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

• integer is_timezoneless (in dt datetime)
  The function returns 1 for timezoneless arguments, zero for timezoned.
  
  
• integer timezone (in dt datetime [, in ignore_tzl integer])
  The function returns timezone offset of its first argument, as an integer value in minutes.
  • if the first argument is timezoneless and second argument is missing or zero then the returned value is NULL.
  • if the first argument is timezoneless and second argument is nonzero then the returned value is 0.
    
    

• datetime adjust_timezone (in dt datetime, in tz_offset integer [, in ignore_tzl integer])
  The function returns its first argument with unchanged GMT value but new timezone offset, as it is specified by the second argument.
  • if the first argument is timezoneless and third argument is missing or zero then error 22023 is signaled.
  • if the first argument is timezoneless and third argument is nonzero then no error is signalled and the argument is handled like it is a GMT value.
    
    
• datetime dt_set_tz (in dt datetime, in tz_offset integer)
  The function returns its first argument with unchanged GMT value but new timezone offset. Unlike adjust_timezone(), if the argument is timezoneless then no error is signalled.
  
  
• datetime forget_timezone (in dt datetime [, in ignore_timezone integer])
  The function returns its first argument as a timezoned value.
  • if the first argument is timezoneless, then it is returned unchanged.
  • if the first argument is timezoned and second argument is missing or zero, then the result is a timezoneless value that "looks like" local time notation.
  • if the first argument is timezoned and second argument is nonzero, then the value is first made GMT and then it becomes timezoneless.
    
    
• datetime now ()
• datetime rdf_now_impl ()
• datetime getdate ()
• datetime get_timestamp ()
• datetime current_timestamp ()
  All these names refer to one function that returns the timestamp of current transaction. It is the datetime of the beginning of current transaction with the fractional part of seconds replaced with serial number of a transaction within the second.
  • If TimezonelessDatetimes=0 then the time has local timezone offset (as it was set at the time of last server start); otherwise it is timezoneless.
    
    
• datetime curdatetime ([in fraction_microseconds integer])
  The function returns current datetime, like now(), but fractional part of seconds can be adjusted by providing the number of "microseconds" as the argument.
  
  
• datetime curdatetimeoffset ([in fraction_microseconds integer])
  The function is like curdatetime(), but the returned datetime is in GMT timezone.
  
  
• datetime curutcdatetime ([in fraction_microseconds integer]) and
  datetime sysutcdatetime ([in fraction_microseconds integer])
  These two names refer to one function that is similar to curdatetime(), but the returned datetime is in GMT timezone.