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