In both cases the conversion is only correct when it is correct.  Microsoft is 
a teeny weeny company that exists and does business only in one time zone and 
has existed for such a short period of time (and produces software which covers 
such short periods of time) that they have never ever encountered a daylight 
savings time rule change.

The only way to convert datetime data on windows is to use a third-party 
package that does it properly, or write it yourself.  Using the WinAPI 
functions is equivalent to "writing it yourself" because they do not actually 
do anything -- you have to manage all the meaningful data and deal with the 
vagaries of the under-documented closed proprietary function implementations in 
windows (which Microsoft even admits do not work properly).


The SystemTimeToTzSpecificLocalTime function takes into account whether 
daylight saving time (DST) is in effect for the local time to which the system 
time is to be converted.

The SystemTimeToTzSpecificLocalTime function may calculate the local time 
incorrectly under the following conditions:

    The time zone uses a different UTC offset for the old and new years.
    The UTC time to be converted and the calculated local time are in different 

> -----Original Message-----
> From: sqlite-users-bounces at [mailto:sqlite-users-
> bounces at] On Behalf Of Vitaly Baranov
> Sent: Wednesday, 9 December, 2015 07:49
> To: sqlite-users at
> Subject: [sqlite] Bug with DATETIME('localtime')
> Hi,
> I've found a bug with using 'localtime' in functions DATETIME(), DATE(),
> TIME().
> Platform: Windows 7.
> Steps to reproduce:
> 1. Set your system time zone as "Russia Time Zone 2, (UTC+03:00) Moscow,
> St. Petersburg, Volgograd)".
> 2. Execute the following script:
> SELECT DATETIME(1414267200, 'unixepoch');
> SELECT DATETIME(1414267200, 'unixepoch', 'localtime');
> Actual results:
> 2014-10-25 20:00:00
> 2014-10-25 23:00:00
> Expected results:
> 2014-10-25 20:00:00
> 2014-10-26 00:00:00
> Current local time in Moscow is "UTC+3", however it was "UTC+4" on this
> date.
> The following is the result of my own investigation, I hope this will be
> useful. sqlite uses C library function localtime(). However Microsoft's
> implementation of this function sometimes returns incorrect value. I
> sent this information to Microsoft (see
> ),
> and they advised to use WinAPI instead of localtime_s(). As far as I can
> see, WinAPI function SystemTimeToTzSpecificLocalTime() works correctly
> for any date.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at

Reply via email to