public inbox for [email protected]help / color / mirror / Atom feed
Issues 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]> 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:06 Adrian Klaver <[email protected]> parent: Nem Tudom <[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:16 Peter J. Holzer <[email protected]> parent: Nem Tudom <[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 22:23 Tom Lane <[email protected]> parent: Peter J. Holzer <[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-28 00:05 Thomas Munro <[email protected]> parent: Nem Tudom <[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