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). Remarks 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 years. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Vitaly Baranov > Sent: Wednesday, 9 December, 2015 07:49 > To: sqlite-users at mailinglists.sqlite.org > 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 > https://connect.microsoft.com/VisualStudio/feedback/details/1984408 ), > 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 mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users