On Thursday, 24 September, 2015 03:36, Dave McKee <dragon at scraperwiki.com> said:
> Hmmm... actually, looks like Windows 10 has regressed back to only having > one set of DST rules... Windows at Vista and later can have an (unlimited) set of DST rules for each Microsoft Time Zone (Microsoft Time Zone's can only be loosely translated into "standard" time zones). However, you are mixing up the HAVING with the USING. While Windows versions at or later than Vista have the provision to store more than a single transition rule in the registry, it only uses one at a time, and that one is NOT based on applicability -- it chooses the latest set of rules in effect TODAY, and uses that one rule to translate all past and future datetimes. This is well documented by Microsoft. If you need to do datetime handling on Windows you will need to store all datetime data in UT1 and use third-party code to handle the translations to and from localtime (for all datetime's that are not "now", as that is the only one Microsoft keeps correct), or otherwise use the registry data to do your own translations. > Manually changed timezone to Pacific US. Results are from before reboot > but don't seem to have changed after. > > David. > > Windows 10 > Dates from https://www.timeanddate.com/time/dst/1985.html > Using sqlite-shell-win32-x86-3081101.zip > ------ > 2012: Sunday, 11 March > sqlite> select datetime("2012-03-10T12:00", 'localtime'); > 2012-03-10 04:00:00 > sqlite> select datetime("2012-03-11T12:00", 'localtime'); > 2012-03-11 05:00:00 > > 2007: Sunday, 11 March > sqlite> select datetime("2007-03-10T12:00", 'localtime'); > 2007-03-10 04:00:00 > sqlite> select datetime("2007-03-11T12:00", 'localtime'); > 2007-03-11 05:00:00 > > 2006: Sunday, 2 April > sqlite> select datetime("2006-04-01T12:00", 'localtime'); > 2006-04-01 05:00:00 > sqlite> select datetime("2006-04-02T12:00", 'localtime'); > 2006-04-02 05:00:00 > sqlite> select datetime("2006-04-03T12:00", 'localtime'); > 2006-04-03 05:00:00 > > sqlite> select datetime("2006-03-11T12:00", 'localtime'); > 2006-03-11 04:00:00 > sqlite> select datetime("2006-03-12T12:00", 'localtime'); > 2006-03-12 05:00:00 > > 1985: Sunday, 28 April > sqlite> select datetime("1985-03-09T12:00", 'localtime'); > 1985-03-09 04:00:00 > sqlite> select datetime("1985-03-10T12:00", 'localtime'); > 1985-03-10 05:00:00 > > > On 22 September 2015 at 17:32, Dave McKee <dragon at scraperwiki.com> wrote: > > > > > ---------- Forwarded message ---------- > > From: Jonathan Moules <jonathan-lists at lightpear.com> > > Date: Mon, Sep 21, 2015 at 5:31 PM > > Subject: [sqlite] Outdated section of docs? > > To: sqlite-users at mailinglists.sqlite.org > > > > > > Hi, > > I was reading this page (http://sqlite.org/lang_datefunc.html), and at > > the very bottom it says: > > / > > / > > > > /"Non-Vista Windows platforms only support one set of DST rules. > > Vista only supports two. Therefore, on these platforms, historical > > DST calculations will be incorrect. For example, in the US, in 2007 > > the DST rules changed. Non-Vista Windows platforms apply the new > > 2007 DST rules to all previous years as well. Vista does somewhat > > better getting results correct back to 1986, when the rules were > > also changed."/ > > > > > > I wonder if that's a section that was written years ago, and the bits > that > > apply to Vista also apply to the Windows releases since then? I don't > know > > enough about Windows Timezone things to be able to find out easily but > this > > reads like it was written back in the era of Vista and probably holds > for > > newer releases too. > > Cheers, > > Jonathan > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
