Nelson's log

sqlite: Unix epoch times

I store all my timestamps in the Unix epoch, as Thompson and Ritchie decreed. sqlite doesn’t really have a date type, instead it has date functions that can work with text or numbers. Unfortunately the numbers are defined with respect to two different epochs.

sqlite’s preferred date format is “Julian day”. This is a floating point number, the number of days since November 24, 4714 B.C., with hours/minutes/seconds being reported in fractional. Ie right now it’s 2457524.189894016. A single second is roughly .00001 in this time scale. sqlite3 reals are 64 bit, so we get 2^52 days or about 140M years of dates where individual seconds have distinguishable timestamps. No practical maximum :-P

Unix and Javascript’s preferred date format is an integer, seconds since Jan 1 1970. Right now it’s 1463330612. Unix times are generally 32 bits and therefore limited to the range of 1970–2038, although we’re all hoping we’ll see an extension to 64 bits before the apocalypse. sqlite3 already seems to have extended support and notes it will work on Unix dates from 0000 to 5352. The sqlite functions also seem to be OK with real number Unix epoch timestamps, with the fraction naming subseconds.

The thing that’s confusing is the sqlite functions like date and strftime operate on Julian day numbers by default. If you want calculations in the Unix epoch instead, you have to pass the flag ‘unixepoch’ to the function.

Some examples:

-- Julian days
select julianday('now');
2457524.19912943
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 2457524.19912943);
2016-05-15 16:46:44.783  Julian:2457524.199129433  Unix:1463330804

-- Unix epoch
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 1463330612, 'unixepoch');
2016-05-15 16:43:32.000  Julian:2457524.196898148  Unix:1463330612
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 1463330612.34, 'unixepoch');
2016-05-15 16:43:32.340  Julian:2457524.196902083  Unix:1463330612
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 0, 'unixepoch');
1970-01-01 00:00:00.000  Julian:2440587.5  Unix:0

-- Max sqlite Unix epoch
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 106750000000, 'unixepoch');
5352-10-09 09:46:40.000  Julian:3676119.907407407  Unix:106750000000
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 106760000000, 'unixepoch');
-141-03-01 13:07:12.700  Julian:1205032.046674768  Unix:-106751991168

-- Gotcha with 'now' and Unix epoch
select strftime('%Y-%m-%d %H:%M:%f  Julian:%J  Unix:%s', 'now', 'unixepoch');
1970-01-29 10:38:44.197  Julian:2440615.943567095  Unix:2457524

That last example is confusing; the time stamp ‘now’ is always the Julian day, even in the context of Unix epoch conversions. So it makes no sense to mix ‘now’ and ‘unixepoch’. I’m sure that’s the source of surprising bugs.

I’m not clear why the year 5352, integer 106750000000 is about the limit of sqlite’s Unix date implementation. That number is about 2^36.635, so it’s not some obvious integer rounding problem. Probably doing calendar arithmetic in floating point somewhere?