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@xxxxxxxxxxx
