SQLite Forum

Date/time formatting

Date/time formatting

(1) By tom (younique) on 2020-05-25 20:53:15 [link] [source]

Reading the documentation about date/time formats, I stumbled about this remark in https://www.sqlite.org/lang_datefunc.html: "Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. ..."

Is this correct in the sense that *only* Vista (but not 7, 8, 10) supports two DST sets? Or is the documentation somewhat outdated and the same applies to Vista-successors as well?

(2) By mistachkin on 2020-05-25 21:40:23 in reply to 1 [link] [source]

I'm not sure if this statement applies to the Win32 API itself or to the MSVCRT function used to interact with it, e.g. localtime().

(3.2) By Keith Medcalf (kmedcalf) on 2020-05-25 22:02:07 edited from 3.1 in reply to 1 [source]

It is complicated. Windows is complicated. And inconsistent.

In versions PRIOR to Windows Vista (that is, WINVER < 0x0600) the registry only allowed one set of translation parameters per registry entry (timezone) and that single rule was applied for all timezone conversions from the Big Bang to the Last Whimper.

In Windows Vista (WINVER between 0x0600 and 0x09FF) the registry format was extended to allow two sets of translation parameters, one that applied to all translations from the Big Bang to "the datetime specified" and one that applied to all translation from "the datetime specified" to the Last Whimper.

Although versions of Windows subsequent to Vista (WINVER >= 0x0A00) permit the registry to hold more than two translations (ie, multiple break datetimes) only the first one is used -- so the behaviour remains the same as Vista even if more complete data is loaded into Windows.

I do not know this for sure, perhaps multiple break dates are handled correctly -- however the default is only to provide a single "break date" (two entries) and by default Microsoft does not provide complete translation data. You would think that if Windows were capable of working properly there would be bazillions of utilities that would permit loading the registry with complete IANA data. But there are not. So I can only conclude that Windows is incapable in this regard.

Contrast this with, for example, Linux (and several other Operating Systems) which load the entire IANA timezone database and correctly process all localtime conversions from the Big Bang to the "present" (new IANA databases are released each time some politicians decide to change the rules, thus "present" is a moving target and requires continuous updating).

I have written an extension to SQLite3 which permits an SQLite3 database to be populated with IANA timezone data and overrides the default datetime functions to handle IANA timezones properly. However it requires modifications to the SQLite3 core itself to enable additional offset tracking hence is not a simple extension (plus, it must be an internal extension since it requires access to SQLite3 internals).

ie, so you can do

select datetime(x, 'Canada/Mountain') from t

and always get a "correct" localtime result. It also changes the default "datetime" format to always show milliseconds and always be localized (ie, have a timezone offset).

(4) By Keith Medcalf (kmedcalf) on 2020-05-25 22:39:58 in reply to 3.2 [link] [source]

According to the Microsoft documentation, Windows only supports a single set of "current timezone translation parameters" that are in effect from the Big Bang to the Last Whimper.

What it does support is a "current" and a "future" set of data defined by the "year". This permits updates to "next years" timezone settings without having to reboot the computer (pffft).

However, the timezone setting applicable to the "current year" are used for all translations from the Big Bang to the Last Whimper.

As I said, Windows is highly inconsistent. Not even Microsoft can figure out how it works, and they supposedly wrote the bloody thing (it cannot be IBM -- OS/2 prior to the "great renaming" when OS/2 got poached by Microsoft and renamed "OS/2 New Technology" and then just "Windows NT" -- used the complete IANA database.

Since that code was written by IBM presumably Microsoft had to re-write it as part of the "Great-X-ing" (DirectX, ActiveX, X this and X that, which was really removing IBM patented technology to which Microsoft had no rights with home-grown replacements) and they did not know how to handle timezones (what, being a little bitty company that only existed in one wee city and only did business from their lemonade stand).

(5) By tom (younique) on 2020-05-26 18:58:01 in reply to 3.2 [link] [source]

Thanks, Keith, I always appreciate your answers and learn much from them. If you ever consider writing a book about SQLite, I'll buy it ;)