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
