MIME-Version: 1.0
References: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com>
In-Reply-To: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com>
From: Thomas Munro <thomas.munro@gmail.com>
Date: Tue, 28 Jan 2025 13:05:37 +1300
Message-ID: <CA+hUKG+awD-hDwp5bL2kLfv_5oVPd6AoWbN4=qEKiRZkzX7ttQ@mail.gmail.com>
Subject: Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
To: Nem Tudom <ellenallhatatlan@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BhUKG%2BawD-hDwp5bL2kLfv_5oVPd6AoWbN4%3DqEKiRZkzX7ttQ%40mail.gmail.com>
Precedence: bulk

On Tue, Jan 28, 2025 at 10:02=E2=80=AFAM Nem Tudom <ellenallhatatlan@gmail.=
com> wrote:
> Any help, advice, recommendations, URL-s, references &c. appreciated.

As others have said, we're using the POSIX AKA Unix time scale, as
almost all general purpose computer systems do.  It's based on the UTC
time scale (the one that has SI seconds of fixed duration defined by
caesium atoms, with extra seconds inserted by committee that should be
displayed as eg 23:59:61 as required to stay within a certain
tolerance of the variable-duration seconds implied by the earth's
actual rotation divided by 86400, known as UT1 or something like
that), except that in POSIX the leap seconds are ignored.  There is a
sort of discontinuous jump, or you might say that the second is
compressed to a duration of 0.  Systems that have good reasons to care
about this stuff often use the TAI time scale (also SI seconds, but
with no leap seconds and thus slowly falling out of sync with the
earth's rotations), or the GPS time scale which is the same except
offset by the number of leap seconds that had been decreed as of 1980
when they invented it and ignoring all new leap seconds after that.
You need an up-to-date table of leap seconds to convert between time
scales, and of course it'd be lossy on eg TAI->POSIX conversions, but
not the reverse.

I showed the bones of how you could do this in SQL here:

https://www.postgresql.org/message-id/CA%2BhUKGLU9Don4YHnfdzn0eeWQsUu8GJDaL=
iUAefLLT6%3DmmeGoQ%40mail.gmail.com

The IERS inserts leap seconds at times that are not expected to
interfere with business, so most people just don't care and the POSIX
time scale is good enough.  That's not always entirely successful:
I've forgotten all the details but once a leap second was inserted at
the moment the Japanese stock market opened, leading to confusion
(looking at the table[1] I think it must have been one of the June
ones where the 30th fell on a business day).  As for how these jumps
in the time scale really happen, there are various approaches
including "smearing" the extra second over a period of time (ie making
the neighbouring seconds shorter for a window of time) so that POSIX
time drifts towards being in sync with UTC over a couple of hours or
something; that works about as well as you'd expect with many
different NTP (etc) implementations using different approaches that
only rarely test these transitions, but again good enough for most
stuff.

The powers that be have agreed to stop adding UTC leap seconds after
2035, so UTC will eventually cease to be "coordinated" (the C) going
forward, and have a fixed offset against TAI and GPS.  The leap second
table will effectively be fixed and only of interest for dealing with
historical times 1972-2035.  And just like TAI and GPS, it'll begin to
drift out of sync with the earth's rotations without further
adjustments, since it's based on SI seconds and the earth is a
spinning chunk of wobbly stardust.

(My memory of all that might be a little fuzzy and I know zilch about
the science of it, but a couple of decades ago I worked on software
that talked to a lot of stock exchanges and we had to worry about when
certain things happened and think about smearing etc.  In practice
time zones were a far bigger source of stress... I recall a local
government suddenly declaring a daylight savings change to suit a
sporting event, etc...)

[1] https://en.wikipedia.org/wiki/Leap_second