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

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(dot)klaver(at)aklaver(dot)com