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');
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?

4 thoughts on “sqlite: Unix epoch times

  1. I think sqlite’s base internal representation is an IEEE double representing time as Julian day (+fraction). I didn’t follow through in depth (there’s only so much time you want to spend in a 3.6 meg C file, even if meticulously commented) but it’s not hard to imagine the contortions required to to ensure the conversions are accurate and lossless in both directions. Makes me wonder how and why they ended up with this design.

  2. A more authoritative answer from the sqlite-dev mailing list –

    Firstly the documentation has a typo; the upper limit is actually 106751991167 (the final 1 is missing in the number you posted).

    The explanation lies in the DateTime struct:

    struct DateTime {
    sqlite3_int64 iJD; /* The julian day number times 86400000 */

    106751991167 * 86400000 = 9223372036828800000
    106751991168 * 86400000 = signed 64-bit overflow

    “But wait,” you say, “the comment says *julian day* times 86400000 and you’re multiplying the *unix epoch” by 86400000 silly.”

    And you have a point. But when the parseDateOrTime function encounters a numeric value, it presumes it is a julian day and stores it in the DateTime.iJD field (multiplying by 86400000). Shortly after, parseModifier comes along and converts iJD from unix-epoch-masquerading-as-julian-day to an actual julian day, but if we started with 106751991168 or higher its already too late.

  3. Oh nice, I was not able to find a public archive and ended up subscribing and asking and someone was kind enough to answer in under an hour. The moral of this story, it seems to me, is that if you care about timestamps (rather than the full glory of actual dates) in sqlite, you might be best off storing something like 64 bit millis and dealing with them in the host runtime.

Comments are closed.