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

[Nem Tudom]

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.

[Nem Tudom]

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!