The manual[1] says "Technically,PostgreSQL uses UT1 rather than UTC
because leap seconds are not handled."  I'm certainly no expert on
this stuff but it seems to me that we are using POSIX time[2] or Unix
time, not UT1.

By my reading, UT1 is one of several time scales of interest to
scientists based on the earth's rotational angle and therefore has
seconds of variable duration detected after the fact using
astronomical observations, whereas POSIX time is a simplification of
the UTC timescale which is based on SI seconds defined by an atomic
clock.  The simplification is that POSIX time completely ignores leap
seconds.  It cannot address leap seconds inserted by UTC, and can
address phantom non-existent seconds if UTC ever deletes a second.

While you could argue that Postgres is time scale agnostic, and that
users are free to consider their time data to refer to points on any
time scale that has strictly 86400 seconds per day, I see two
arguments for the time scale being POSIX in particular:

1.  We tolerate UTC leap seconds like '23:59:60' on input.  Then we
throw away some information by shifting it into the 'next' second,
because our model can't represent the leap second itself (though we
could: we have the table of leap seconds at
src/timezone/data/leapseconds).  That string does not name a valid
time on the TAI, GPS, UT0, UT1, UT1R or UT2 time scales.  It does name
a valid UTC time (at least on certain dates), and fits with the theory
that we are using POSIX-style simplified UTC.  We're actively doing
UTC-with-gaps (like most software).

2.  While your computer's implementation of gettimeofday() may in
theory be hooked up to whatever gizmo is needed to measure the earth,
in practice all computers try to approximate or track atomic clocks.
The duration of a second is based on caesium atoms, not observations
of a wobbly planet.  If Postgres were somehow using UT1, then now()
would be up to 0.9 seconds away from the UTC time you see on
electronic devices you see all around you (the maximum drift the UTC
people allow before they add or delete a second).  What actually
happens is that gettimeofday() tracks UTC, except during the bits that
POSIX time can't address (and immediately nearby where various
strategies are used to wallpaper over the gaps with time smearing
algorithms, depending on your OS, ntpd etc which most people never
have to worry about, unless, for example, they are running a stock
exchange which needs to open immediately after a leap second as
happened in Japan recently, in which case they should worry).

[1] https://www.postgresql.org/docs/9.5/static/view-pg-timezone-names.html
[2] https://en.wikipedia.org/wiki/Unix_time

Thomas Munro

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to