On Tue, 23 May 2006, Chris Werner wrote:
Hello,
I am just curious about SQLite's date and time manipulation functions. I am
using the sqlite3 command line interface on the above described platform.
When I select a datetime for 2^47:
sqlite> SELECT datetime(140737488355328, 'unixepoch');
4461763-06-20 05:22:07
Adding a second to the time [2^47 +1] seems to add 2 seconds to the
return??
sqlite> SELECT datetime(140737488355329, 'unixepoch');
4461763-06-20 05:22:09
I suspect an overflow of some sort, but I cannot phrase it concisely.
Datetime in SQLite is represented as a 64 bit floating point value. The
units are seconds since the unix epoch.
What you're seeing is the limited precision (48 bits I believe) of 64 bit
floating point numbers. Not a problem for real world values, but a problem
if high sub-second precision or long distant dates are needed.
Date time seems to return reasonable values up until 185327782012799 [you
tell me], after which the return format is not a valid date.
Again, I suspect an overflow of some sort, can anyone explain?
sqlite> SELECT datetime(185327782012799, 'unixepoch');
5874773-08-15 23:59:58
sqlite> SELECT datetime(185327782012800, 'unixepoch');
-5884205--1-00 00:00:00
Not sure about this one. Check out computeYMD() in date.c. It has some
pretty funky calculations to work out the year from the time. I won't
pretend to pretend what all the figures are for, but there is probably
some 32 bit integer overflow that messes up the calculations.
Potential bug note:
I notice all the variables in computeYMD() and computeJD() use integer
intermediate values, yet all the intermediate values might be best off
being held in real value variables due to the use of floating point
arithmatic. Is there a real threat of wrong dates coming from this?
I can understand the use of integers from a performance POV, and some
small embedded processors have no FPU. Perhaps there could be a compile
time flag to choose between ints and doubles for these intermediate
results?
Not a critical item, nor important enough to be reported as a bug,
Just curious,
Christian Werner
