rather unintuitively excel uses the OLE automation timestamp that records the number of days since 1899/12/30
http://sandersonforensics.com/forum/content.php?131-A-brief-history-of-time-stamps https://msdn.microsoft.com/en-us/library/system.datetime.tooadate(v=vs.110).aspx now no need for the ugly +2 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 13 December 2016 at 13:19, Daniel Dumitriu <daniel.dumit...@gmail.com> wrote: >> But that does not seems to be the whole story, since if I do: >> >> SELECT datetime(42713.19166666667+julianday('1900-01-01')); >> >> I get 2016-12-11 04:36:00 instead of what you were expecting >> 2016-12-09 05:15. Something is a little off. I suspect that there > I think he is expecting 2016-12-09 04:36 (column "Excel values" looks > somehow reversed), so the time part is working. > >> You are apparently wanting to use the Excel date format, which appears >> to be unique to Excel and used nowhere else. According to the >> Microsoft documentation >> (https://support.microsoft.com/en-us/kb/214094) Excel for Windows uses >> 1900-01-01 as its epoch and advances the value by 1.0 per day. Excel >> for Mac reportedly uses 1904-01-01 as the epoch. So straight away we >> see that you are going to need to modify your code depending on >> whether you are running on Windows or Mac. > Apparently one needs to add 2 to that value, since: > - 1900-01-01 in Excel (on Windows) gets value 1, not 0 > - Excel notoriously and incorrectly considers 1900 a leap year [1] and > acknowledges it, but "won't change" [2] > > So you should probably try > > SELECT datetime(2 + 42713.19166666667+julianday('1900-01-01')); > > Regards, > Daniel > > [1] https://en.wikipedia.org/wiki/Leap_year_bug > [2] https://support.microsoft.com/en-us/kb/214326 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
