From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
cc: Nem Tudom <ellenallhatatlan@gmail.com>, pgsql-general@lists.postgresql.org
Subject: Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
In-reply-to: <20250127211657.b7gbkfgyjpvdokes@hjp.at>
References: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com> <20250127211657.b7gbkfgyjpvdokes@hjp.at>
Comments: In-reply-to "Peter J. Holzer" <hjp-pgsql@hjp.at>
	message dated "Mon, 27 Jan 2025 22:16:57 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <739280.1738016587.1@sss.pgh.pa.us>
Date: Mon, 27 Jan 2025 17:23:07 -0500
Message-ID: <739281.1738016587@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/739281.1738016587%40sss.pgh.pa.us>
Precedence: bulk

"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