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

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