SQLite Forum

Date/time formatting
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).