Thread

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

Ellen Allhatatlan <ellenallhatatlan@gmail.com> — 2025-01-27T21:01:59Z


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...

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

Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-27T21:06:47Z

On 1/27/25 13:01, Nem Tudom wrote:
> 
> 
> 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.

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.)"
> 
> 
> E...
> 
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-01-27T21:16:57Z

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.
> 
> 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?

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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

Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-27T21:47:04Z

On 1/27/25 13:23, Nem Tudom wrote:

Reply to list also.
Ccing list.

See post from Peter Holzer .

> 
> 
> Hi Adrian, all,
> 
> 
>>> Any help, advice, recommendations, URL-s, references &c. appreciated.
> 
>> https://www.postgresql.org/docs/current/functions-datetime.html
>> https://www.postgresql.org/docs/current/view-pg-timezone-names.html
> 
>> " (Technically, PostgreSQL does not use UTC because leap seconds are 
>> not handled.)"
> 
> Well, that was sweet and to the point! :-) Thanks for your input!
> 
> Does this cause any issues interfacing with other systems?
> 
> Hmmm... it appears not:
> 
> https://dba.stackexchange.com/questions/105514/leap-second-in-database-system-postgresql-and-sql-server
> 
> and
> 
> https://stackoverflow.com/questions/31136211/how-to-handle-leap-seconds-in-oracle
> 
> So, it would appear that neither Oracle nor Microsoft (RDBMS or OS/SQL 
> Server) worry too much about this, so it would appear that I shouldn't 
> either.
> 
> However, I think this means that there's 27 seconds of my life that I 
> never knew I had...
> 
> Thanks again, saves me some work, and rgs,
> 
> 
> E...
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-27T22:23:07Z

"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2025-01-27 21:01:59 +0000, Nem Tudom wrote:
>> I thought that the EPOCH was the number of seconds since 1970-01-01
>> 00:00:00? Is this incorrect?

> 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".

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

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

Thomas Munro <thomas.munro@gmail.com> — 2025-01-28T00:05:37Z

On Tue, Jan 28, 2025 at 10:02 AM 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%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

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

Ellen Allhatatlan <ellenallhatatlan@gmail.com> — 2025-01-28T14:00:20Z

On 28/01/2025 00:05, Thomas Munro wrote:

Thanks to you and all the others who took the trouble to reply,

> 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 technical explanation is much appreciated - I'd gathered (more or 
less) as much from my searching and reading the leap second Wiki.

So, the situation is that, basically, leap seconds are "fudged" to use 
the technical term!

I asked the question with a view to having accurate TIMESTAMP 
differences - i.e. to the second. However, since everyone is fudging 
(incl. AFAICS Oracle and SQL Server), this means that accepting the 
status quo will just make my inaccuracies will be the same as everyone 
else's, ergo I'm golden!

At least that's one issue that I can safely ignore - I didn't fancy 
implementing this on my own.

Thanks again and rgs,

E!