On 12/12/16, Lipson, Ed <ed.lip...@bnymellon.com> wrote: > Python 32bit, Windows 7 64bit > How do I turn a real into a date?
That depends on how the real number represents a date. SQLite supports two very popular encodings: Julian Day Number (https://en.wikipedia.org/wiki/Julian_day) and Unix Time (https://en.wikipedia.org/wiki/Unix_time). 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. 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 are some quirks in the Excel date/time conversion. Probably some reverse engineering is going to be required to successfully convert Excel-for-Windows dates. Maybe somebody else on the list can provide further guidance. OT: You really should consider using ISO-8601 dates everywhere. Otherwise, I don't know if 12/09/2016 means December 9 or September 12 without consulting your $LOCALE. Also, ISO-8601 dates have the virtue that chronological order and lexicographical order are the same. > I'm reading an XLSX with openpyxl and > writing the sheet into a table. The real value is preserved into the > database correctly. When I apply the date function, I get a negative year. I > have not seen anything on the net about how to get the query to return the > proper values. > > select key, created, typeof(created), date(Created) , time(created) > from JIRA_Stat_0_20161212081613 limit 5 > > TSGOPM-495 42713.19166666667 real -4596-11-04 > 16:36:00 > TSGOPM-496 42713.19236111111 real -4596-11-04 > 16:37:00 > TSGOPM-497 42713.19583333333 real -4596-11-04 > 16:42:00 > TSGOPM-498 42713.19652777778 real -4596-11-04 > 16:43:00 > TSGOPM-499 42713.21875 real -4596-11-04 > 17:15:00 > > Excel values. > Created > 12/9/2016 5:15 > 12/9/2016 4:43 > 12/9/2016 4:42 > 12/9/2016 4:37 > 12/9/2016 4:36 > > > Thanks, > Ed Lipson > Managing Director > BNY Mellon | 2 Hanson Pl AIM 111-0800 | Brooklyn NY 11217 > Information Lifecycle Management and Database Administration > T 718.315.4763 | F 724.540.6622 | C 917.859.5180 | > ed.lip...@bnymellon.com<mailto:ed.lip...@bnymellon.com> > > > The information contained in this e-mail, and any attachment, is > confidential and is intended solely for the use of the intended recipient. > Access, copying or re-use of the e-mail or any attachment, or any > information contained therein, by any other person is not authorized. If you > are not the intended recipient please return the e-mail to the sender and > delete it from your computer. Although we attempt to sweep e-mail and > attachments for viruses, we do not guarantee that either are virus-free and > accept no liability for any damage sustained as a result of viruses. > > Please refer to http://disclaimer.bnymellon.com/eu.htm for certain > disclosures relating to European legal entities. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
