public inbox for [email protected]
help / color / mirror / Atom feedIssues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
5+ messages / 5 participants
[nested] [flat]
* Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
@ 2025-01-27 21:01 Nem Tudom <[email protected]>
2025-01-27 21:06 ` Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Adrian Klaver <[email protected]>
2025-01-27 21:16 ` Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Peter J. Holzer <[email protected]>
2025-01-28 00:05 ` Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Thomas Munro <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: Nem Tudom @ 2025-01-27 21:01 UTC (permalink / raw)
To: pgsql-general
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
2025-01-27 21:01 Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Nem Tudom <[email protected]>
@ 2025-01-27 21:06 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2025-01-27 21:06 UTC (permalink / raw)
To: Nem Tudom <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
2025-01-27 21:01 Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Nem Tudom <[email protected]>
@ 2025-01-27 21:16 ` Peter J. Holzer <[email protected]>
2025-01-27 22:23 ` Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Tom Lane <[email protected]>
2 siblings, 1 reply; 5+ messages in thread
From: Peter J. Holzer @ 2025-01-27 21:16 UTC (permalink / raw)
To: [email protected]
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.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
2025-01-27 21:01 Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Nem Tudom <[email protected]>
2025-01-27 21:16 ` Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Peter J. Holzer <[email protected]>
@ 2025-01-27 22:23 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-01-27 22:23 UTC (permalink / raw)
To: Peter J. Holzer <[email protected]>; +Cc: Nem Tudom <[email protected]>; [email protected]
"Peter J. Holzer" <[email protected]> writes:
> On 2025-01-27 21:01:59 +0000, Nem Tudom wrote:
>> 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".
I'm not sure what POSIX says about this, but that is the definition
Postgres uses --- and we won't let you select a timezone setting
that does account for leap seconds.
postgres=# set timezone = 'America/New_York';
SET
postgres=# set timezone = 'right/America/New_York';
ERROR: time zone "right/America/New_York" appears to use leap seconds
DETAIL: PostgreSQL does not support leap seconds.
If we did support that, it'd enormously complicate all timestamp
arithmetic --- and we could hardly do calculations with times in
the future at all, given the uncertainty around when leap seconds
will be declared. So if you want to do astronomical timekeeping,
you should use some other data type than timestamptz.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
2025-01-27 21:01 Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Nem Tudom <[email protected]>
@ 2025-01-28 00:05 ` Thomas Munro <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Thomas Munro @ 2025-01-28 00:05 UTC (permalink / raw)
To: Nem Tudom <[email protected]>; +Cc: pgsql-general
On Tue, Jan 28, 2025 at 10:02 AM Nem Tudom <[email protected]> wrote:
> Any help, advice, recommendations, URL-s, references &c. appreciated.
As others have said, we're using the POSIX AKA Unix time scale, as
almost all general purpose computer systems do. It's based on the UTC
time scale (the one that has SI seconds of fixed duration defined by
caesium atoms, with extra seconds inserted by committee that should be
displayed as eg 23:59:61 as required to stay within a certain
tolerance of the variable-duration seconds implied by the earth's
actual rotation divided by 86400, known as UT1 or something like
that), except that in POSIX the leap seconds are ignored. There is a
sort of discontinuous jump, or you might say that the second is
compressed to a duration of 0. Systems that have good reasons to care
about this stuff often use the TAI time scale (also SI seconds, but
with no leap seconds and thus slowly falling out of sync with the
earth's rotations), or the GPS time scale which is the same except
offset by the number of leap seconds that had been decreed as of 1980
when they invented it and ignoring all new leap seconds after that.
You need an up-to-date table of leap seconds to convert between time
scales, and of course it'd be lossy on eg TAI->POSIX conversions, but
not the reverse.
I showed the bones of how you could do this in SQL here:
https://www.postgresql.org/message-id/CA%2BhUKGLU9Don4YHnfdzn0eeWQsUu8GJDaLiUAefLLT6%3DmmeGoQ%40mail...
The IERS inserts leap seconds at times that are not expected to
interfere with business, so most people just don't care and the POSIX
time scale is good enough. That's not always entirely successful:
I've forgotten all the details but once a leap second was inserted at
the moment the Japanese stock market opened, leading to confusion
(looking at the table[1] I think it must have been one of the June
ones where the 30th fell on a business day). As for how these jumps
in the time scale really happen, there are various approaches
including "smearing" the extra second over a period of time (ie making
the neighbouring seconds shorter for a window of time) so that POSIX
time drifts towards being in sync with UTC over a couple of hours or
something; that works about as well as you'd expect with many
different NTP (etc) implementations using different approaches that
only rarely test these transitions, but again good enough for most
stuff.
The powers that be have agreed to stop adding UTC leap seconds after
2035, so UTC will eventually cease to be "coordinated" (the C) going
forward, and have a fixed offset against TAI and GPS. The leap second
table will effectively be fixed and only of interest for dealing with
historical times 1972-2035. And just like TAI and GPS, it'll begin to
drift out of sync with the earth's rotations without further
adjustments, since it's based on SI seconds and the earth is a
spinning chunk of wobbly stardust.
(My memory of all that might be a little fuzzy and I know zilch about
the science of it, but a couple of decades ago I worked on software
that talked to a lot of stock exchanges and we had to worry about when
certain things happened and think about smearing etc. In practice
time zones were a far bigger source of stress... I recall a local
government suddenly declaring a daylight savings change to suit a
sporting event, etc...)
[1] https://en.wikipedia.org/wiki/Leap_second
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-28 00:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-27 21:01 Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds. Nem Tudom <[email protected]>
2025-01-27 21:06 ` Adrian Klaver <[email protected]>
2025-01-27 21:16 ` Peter J. Holzer <[email protected]>
2025-01-27 22:23 ` Tom Lane <[email protected]>
2025-01-28 00:05 ` Thomas Munro <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox