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
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.
-- 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?