Date: Mon, 27 Jan 2025 22:16:57 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Issues with EPOCH-s, TIMESTAMP(TZ)-s and leap seconds.
Message-ID: <20250127211657.b7gbkfgyjpvdokes@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="wb2i6o2euxc7wyxm"
Content-Disposition: inline
In-Reply-To: <c3520c84-33d3-47f2-a859-091f369bdc91@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20250127211657.b7gbkfgyjpvdokes%40hjp.at>
Precedence: bulk


--wb2i6o2euxc7wyxm
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2025-01-27 21:01:59 +0000, Nem Tudom wrote:
> I'm having trouble understanding matters related to TIMESTAMP(TZ)-s and l=
eap
> seconds - my machine runs on UTC so as to remove any issues related to the
> zones.
>=20
> From here: https://en.wikipedia.org/wiki/Leap_second,
>=20
> There have been 27 leap seconds added to UTC since 1972.
>=20
>=20
> But, when I run this fiddle (see bottom of this email link)
>=20
> https://dbfiddle.uk/wxvmzfJb
>=20
> (first snippet - 2015 -> 2016) I get a "nice" even number for the EPOCH o=
f,
> 00:00:00 2016 , say (=3D 1451606400) - now, with 27 leap seconds since 19=
72, I
> would expect that number to be (something like) 1451606427?
>=20
> 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

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--wb2i6o2euxc7wyxm
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmeX98MACgkQ8g5IURL+
KF3fTA//USeu6rTjTEUWqE/QBJHEgThsI5fLeKqhfAXqzCUrE2Dr10zEWd4LS22T
ddzRArgqAgOkaF8Kpp1tnT3OqK04j+wigxCI+vsVmkjT/4Z6iIHl4vvqfHZs6zko
N6bc+IOKh+gg+ELH0VO4GDaTX5q+046iuI8zBhQxo3t4IrAc94OiKrNg9pn5jLtM
ELAst0K/QFjSdT/nL4v1T1diQQW5CKZEiBeAd41UcKT8DvRac4wGfrVN6y5cQ1tL
r1cKMreP2LfMqrqiZtfadyoZqxxEJ+m0ipveNkWothtE7Rf553pb9hmrVTMO59QD
He9zppoeiDxtyRh9TVjOHJV0mP5Dw1LcQjD3pPi9tahsFNV6Kyp01Fl3aOMZEric
crgLZ8TJV9AzKSm/gW94sJkYyfSBBVHMxHKi3AAibTTnSUZACiy4zPRMia5YY6MJ
Ujw50CVQkXdJGN3qXll7fUY/GiQ/yWrnnskYCGaL/i0LTBG4NVOLpCgfV5CIwfh0
zgU+ad8rrjm6Fqr0JWokk8i5LEDNHv5lk/tDsI+879EEkP/HxBsou9EtQ5NSHYVd
iSDqKZ91Gdd8TQ9Dtja6Qac+UsFJga1NNbuUar/ri273NqrQJl7vz7dW+3mVhLM+
GQ/0x871I9Y5/iqqE+6XdOkHHezr7EVyeIETY6XpVwvcOhP1ARU=
=ArcY
-----END PGP SIGNATURE-----

--wb2i6o2euxc7wyxm--