This has not much to do with the original question, but as physicist I
cannot resist:

National institutes (NIST in the US, NPL in the UK, PTB in Germany, to name
just a few) provide reference times in UTC, which are distributed nowadays
also via the internet, e.g. the NTP protocol. Therefore clocks of
computers, smart phone etc. are, if at all, synchronized more or less
successfully to UTC, and the timestamps that a software like Sqlite handles
are in the vast majority UTC, possibly plus a timezone offset. For example,
the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC

What can go wrong?:

1) The local clock is synchronized now and then, in some cases as a
consequence of the well-know leap seconds, by setting it abruptly to a new
time. Obviously this does not guarantee that the timestamps become ordered
the same as the events really happened. In the worst case a timestamp has
the wrong day or even the wrong year (with potentially legal consequences,
e.g. for financial transactions).

When is it right?:

2) Using the information from NTP, only the clock speed is adjusted to
compensate for drifts. Leap seconds are announced in advance via NTP. But
none of the major operating systems, Windows, Linux, Unix can internally
represent times within leap seconds. Therefore the system clock is halted
for the leap second. Calls for the system time within a leap second return
time stamps just before the leap second, having a small difference between
them such that their order is correct.

Sqlite and applications are here at the mercy of the underlying system, no
matter how the time at the Sqlite level is presented, as floating point
Julian day numbers, (milli- or micro) second counters from a certain point
in time (epoch) or so. Normally leap seconds don't need to be
representable, as Sqlite/applications are not going to get exposed to such
time stamps (all the OSs cannot). But any timestamps are almost certainly
(supposed to be) UTC, plus timezone offset.

Finally UT1:

1) Unless you need to do stuff like tracking satellites, planets, stars and
other celestial objects with high precision from the Earth, you don't need
to know what it is.

2) UT1 is published by the as a daily correction in
SI seconds to UTC, distinguishing between predicted and final correction.
As such UT1 does not have seconds, days etc. You can of course define a UT1
day as between the times when the corrected UTC passes midnight, and then
divide this "day" into 86400 "seconds". From the physical viewpoint this
would be a bit weird because these seconds then have a different length
than the standard SI second, and their length also varies from day to day.
sqlite-users mailing list

Reply via email to