Thread: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
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...
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@aklaver.com
On 1/27/25 13:23, Nem Tudom wrote: Reply to list also. Ccing list. See post from Peter Holzer . > > > Hi Adrian, all, > > >>> Any help, advice, recommendations, URL-s, references &c. appreciated. > >> https://www.postgresql.org/docs/current/functions-datetime.html >> https://www.postgresql.org/docs/current/view-pg-timezone-names.html > >> " (Technically, PostgreSQL does not use UTC because leap seconds are >> not handled.)" > > Well, that was sweet and to the point! :-) Thanks for your input! > > Does this cause any issues interfacing with other systems? > > Hmmm... it appears not: > > https://dba.stackexchange.com/questions/105514/leap-second-in-database-system-postgresql-and-sql-server > > and > > https://stackoverflow.com/questions/31136211/how-to-handle-leap-seconds-in-oracle > > So, it would appear that neither Oracle nor Microsoft (RDBMS or OS/SQL > Server) worry too much about this, so it would appear that I shouldn't > either. > > However, I think this means that there's 27 seconds of my life that I > never knew I had... > > Thanks again, saves me some work, and rgs, > > > E... > > > > -- Adrian Klaver adrian.klaver@aklaver.com
"Peter J. Holzer" <hjp-pgsql@hjp.at> 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
On Tue, Jan 28, 2025 at 10:02 AM Nem Tudom <ellenallhatatlan@gmail.com> 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.gmail.com 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
On 28/01/2025 00:05, Thomas Munro wrote: Thanks to you and all the others who took the trouble to reply, > I showed the bones of how you could do this in SQL here: > https://www.postgresql.org/message-id/CA%2BhUKGLU9Don4YHnfdzn0eeWQsUu8GJDaLiUAefLLT6%3DmmeGoQ%40mail.gmail.com The technical explanation is much appreciated - I'd gathered (more or less) as much from my searching and reading the leap second Wiki. So, the situation is that, basically, leap seconds are "fudged" to use the technical term! I asked the question with a view to having accurate TIMESTAMP differences - i.e. to the second. However, since everyone is fudging (incl. AFAICS Oracle and SQL Server), this means that accepting the status quo will just make my inaccuracies will be the same as everyone else's, ergo I'm golden! At least that's one issue that I can safely ignore - I didn't fancy implementing this on my own. Thanks again and rgs, E!