Subject: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.

From: Nem Tudom <[email-masked]>
Date: Mon, 27 Jan 2025 21:01:59 +0000

Hi all,

I'm having trouble understanding matters related to TIMESTAMP(TZ)-s and 
leap seconds - my machine runs on UTC so as to remove any issues related 
to the zones.

 From here: https://en.wikipedia.org/wiki/Leap_second,

There have been 27 leap seconds added to UTC since 1972.

But, when I run this fiddle (see bottom of this email link)

https://dbfiddle.uk/wxvmzfJb

(first snippet - 2015 -> 2016) I get a "nice" even number for the EPOCH 
of, 00:00:00 2016 , say (= 1451606400) - now, with 27 leap seconds since 
1972, I would expect that number to be (something like) 1451606427?

I thought that the EPOCH was the number of seconds since 1970-01-01 
00:00:00? Is this incorrect?

Also, (first snippet again), why is the TIMESTAMPTZ 23:59:60 2015 even 
allowed?

Now, we come to the second snippet (2016 -> 2017), I get *_exactly_* the 
same behaviour!

I was expecting to see that '2016-12-31 23:59:60'::TIMESTAMPTZ would 
work (leap second) and then that '2017-01-01 00:00:00'::TIMESTAMPTZ 
would have incremented by 1 second?

I'm puzzled. Does PostgreSQL take leap seconds into account? Does anyone?

Any help, advice, recommendations, URL-s, references &c. appreciated.

E...

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 27 Jan 2025 13:06:47 -0800

> Any help, advice, recommendations, URL-s, references &c. appreciated.

https://www.postgresql.org/docs/current/functions-datetime.html

"timezone

     The time zone offset from UTC, measured in seconds. Positive values 
correspond to time zones east of UTC, negative values to zones west of 
UTC. (Technically, PostgreSQL does not use UTC because leap seconds are 
not handled.)
"

https://www.postgresql.org/docs/current/view-pg-timezone-names.html

" (Technically, PostgreSQL does not use UTC because leap seconds are not 
handled.)"

-- 
Adrian Klaver
[email-masked]

---

From: "Peter J. Holzer" <[email-masked]>
Date: Mon, 27 Jan 2025 22:16:57 +0100

On 2025-01-27 21:01:59 +0000, Nem Tudom wrote:
> I'm having trouble understanding matters related to TIMESTAMP(TZ)-s and leap
> seconds - my machine runs on UTC so as to remove any issues related to the
> zones.
> 
> (first snippet - 2015 -> 2016) I get a "nice" even number for the EPOCH of,
> 00:00:00 2016 , say (= 1451606400) - now, with 27 leap seconds since 1972, I
> would expect that number to be (something like) 1451606427?
> 

The POSIX standard mandates that leap seconds must be ignored. It's not
really "number of seconds since 1970-01-01", but "number of days since
1970-01-01 times 86400 plus number of seconds in the current day".

So you can't use epoch to detect leap seconds.

And I don't think PostgreSQL keeps track of leap seconds internally
either, so that information simply isn't there to begin with.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Tom Lane <[email-masked]>
Date: Mon, 27 Jan 2025 17:23:07 -0500

> 1970-01-01 times 86400 plus number of seconds in the current day".

I'm not sure what POSIX says about this, but that is the definition
Postgres uses --- and we won't let you select a timezone setting
that does account for leap seconds.

postgres=# set timezone = 'America/New_York';
SET
postgres=# set timezone = 'right/America/New_York';
ERROR:  time zone "right/America/New_York" appears to use leap seconds
DETAIL:  PostgreSQL does not support leap seconds.

If we did support that, it'd enormously complicate all timestamp
arithmetic --- and we could hardly do calculations with times in
the future at all, given the uncertainty around when leap seconds
will be declared.  So if you want to do astronomical timekeeping,
you should use some other data type than timestamptz.

			regards, tom lane

---

From: Thomas Munro <[email-masked]>
Date: Tue, 28 Jan 2025 13:05:37 +1300

> 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%2BhUKGLU9Don4YHnfdzn0eeWQsUu8GJDaLiUAefLLT6%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