Feedback-ID: i76984098:Fastmail
Message-ID: <5c09e4b5-1b00-41e6-a56c-ce1c10a3f1de@aklaver.com>
Date: Mon, 27 Jan 2025 13:06:47 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
To: Nem Tudom <ellenallhatatlan@gmail.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/5c09e4b5-1b00-41e6-a56c-ce1c10a3f1de%40aklaver.com>
Precedence: bulk

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