date function broken on Windows

(1) By anonymous on 2024-08-16 13:06:56 [link] [source]

SELECT date(1748528160); returns nothing with the latest version on Windows 11.

Is this a bug or did something change? The documentation does not explain, why it could fail. The value is from https://www.sqlite.org/lang_datefunc.html

SQLite 3.46.0 2024-05-23 13:25:27 96c92aba00c8375bc32fafcdf12429c58bd8aabfcadab6683e35bbb9cdebf19e zlib version 1.3 msvc-1939 (64-bit)

(2) By SeverKetor on 2024-08-16 13:20:47 in reply to 1 [link] [source]

For reasons I feel like I knew at one point but no longer do, you have to use date(1748528160, 'unixepoch'). Ran into this myself recently

(3) By Richard Hipp (drh) on 2024-08-16 13:25:25 in reply to 1 [source]

Without any additional modifier arguments, the first argument to date() is interpreted as a Julian Day Number if it numeric, or as an ISO 8601 date string if the argument is TEXT.

Your argument of 1,748,528,160 as a Julian Day comes out to sometime in the year of our Lord 4,782,627. That is outside the range of dates that SQLite can deal with, so it returns NULL.

You are apparently expecting the argument to be interpreted as a unix timestamp (the number of seconds since 1970-01-01 00:00:00Z). Date() will do that, but you have to give it a modifier argument, either 'auto' or 'unixepoch'.

SELECT date(1748528160,'auto');
SELECT date(1748528190,'unixepoch');

Both give you the same answer: 2025-05-29.

(5) By anonymous on 2024-08-16 13:30:30 in reply to 3 [link] [source]

Thanks, with this it works now. Interesting, that it worked without that on older versions on MacOS.

(6) By jose isaias cabrera (jicman) on 2024-08-16 14:17:43 in reply to 5 [link] [source]

if you want to see local time,

SELECT date(1748528160,'auto','localtime');
SELECT date(1748528190,'unixepoch','localtime');
SELECT datetime(1748528160,'auto');
SELECT datetime(1748528190,'unixepoch');
SELECT datetime(1748528160,'auto','localtime');
SELECT datetime(1748528190,'unixepoch','localtime');

I use this all the time because I want to see the actual date and time. But, I am crazy like that. :-)

(4) By Stephan Beal (stephan) on 2024-08-16 13:27:50 in reply to 1 [link] [source]

The documentation does not explain, why it could fail. The value is from https://www.sqlite.org/lang_datefunc.html

From that page:

These three formats are collectively known as time-values. All of the date time functions accept time-values as either ISO-8601 text or as Julian day numbers. They can also be made to accept unix timestamps by adding optional modifiers arguments 'auto' or 'unixepoch'.