Sqlite provides in its SQL the julianday function, which can serve as a timestamp with a good resolution (millisecs, double precision floating point numbers are used) and for times way back into the past and far into the future. However, as presently implemented, the function does not really return the Julian day as it is recommended by the IAU (International Astronomical Union).
The time available in computers (smartphones etc) is normally UTC and a local time offset, which is sufficient for almost all times, but not quite all. On average every 18 months leap seconds are inserted. Events during these leap seconds cannot be unambiguously timestamped by a representation of time based on UTC. According to the IAU the Julian day, JD, should be calculated from a date/time presentations using TT (terrestrial time) rather than UTC. TT is presently 67.184 s ahead of UTC, this changes with time, especially with every inserted leap second. What could be done about this in Sqlite: 1) do nothing, or maybe just document more clearly, that the julianday in Sqlite, for date/time input in UTC, returns JD(UTC) in IAU terminology, which, for example, does give time differences (or nr of days elapsed since epoch) ignoring leap seconds and is off by about 1 minute with respect to astronomical JD. 2) introduce (an) additional function(s) that return an also in leap seconds increasing time stamp based on TAI (temps atomic international)/TT. Because the underlying OSs normally don't provide the necessary information, sqlite would have to keep a list of the so far 25 inserted leap seconds, and it would need to be upgraded for every future leap second going to be inserted. The SOFA library backed by the IAU (http://www.iausofa.org) uses this approach with respect to handling the leap seconds. GPS related software packages are doing it similarly. Sqlite would, AFAIK, be the first general database supporting a correct handling of timestamps with respect to leap seconds, if something like option 2) above is adopted. I would think it would be nice to have at least in Sqlite4. Stephan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
