Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vwJ0G-00G1BQ-1v for pgsql-general@arkaria.postgresql.org; Sat, 28 Feb 2026 12:01:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vwJ0E-00Aknt-0h for pgsql-general@arkaria.postgresql.org; Sat, 28 Feb 2026 12:00:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vwJ0D-00Aknl-2s for pgsql-general@lists.postgresql.org; Sat, 28 Feb 2026 12:00:57 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vwJ0A-00000001it9-2Io4 for pgsql-general@lists.postgresql.org; Sat, 28 Feb 2026 12:00:57 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 195A917DB0; Sat, 28 Feb 2026 13:00:54 +0100 (CET) Date: Sat, 28 Feb 2026 13:00:54 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Timezone handling with timestamp without time zone columns Message-ID: <7eb7di2cb63iryenre6vc5bzqswpbna5rriazrn4zquppj5zqb@4whytmyvvmet> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <8c3b8558fc3322c31d9f05517dcdb43e16296fa0.camel@cybertec.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="jvcljfmy4ffojfpo" Content-Disposition: inline In-Reply-To: <8c3b8558fc3322c31d9f05517dcdb43e16296fa0.camel@cybertec.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --jvcljfmy4ffojfpo Content-Type: text/plain; protected-headers=v1; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Subject: Re: Timezone handling with timestamp without time zone columns MIME-Version: 1.0 On 2026-02-27 16:01:10 +0100, Laurenz Albe wrote: > The best practice is that you store tmestamps in a consistent fashion: ACK. > either >=20 > - use "timestamp with time zone", store timestamps with time zone > and make sure that the parameter "timezone" is set correctly in each > database session >=20 > or >=20 > - use "timestamp without time zone" and store only Chicago timestamps > without a time zone Be aware though that in timezones with DST there is one hour in autumn which cannot be properly represented: hjp=3D> set timezone =3D 'Canada/Pacific'; SET hjp=3D> select '2025-11-02 08:23Z'::timestamptz::timestamp; =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97 =E2=95=91 timestamp =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2 =E2=95=91 2025-11-02 01:23:00 =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D (1 row) hjp=3D> select '2025-11-02 09:23Z'::timestamptz::timestamp; =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97 =E2=95=91 timestamp =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2 =E2=95=91 2025-11-02 01:23:00 =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D (1 row) (OTOH sometimes you need the local time without any reference to a time zone or at least without a UTC offset) hjp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --jvcljfmy4ffojfpo Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmmi2O8ACgkQ8g5IURL+ KF339Q//dt9rEBaL+SFhp3qW8G5/0QH4ldTgKOfaVxzQAOElNpoGh5qaDgpVCVG/ +g5itQWsfZFUh/MGzXEkRed+csWKHB/coz47uyqbpN13X5oPHV9bYkVrnHNZcoCG F486VZsxeGSOozQU8myJ4euzur2OYZ8174tFF1Vhryt5YuQyhc9hdPIq4vFPFXk6 7Ph8vn3lOyS+3w+uKOyNq7HxG6YIjj7ZTc6SnV0i5epWMHLMcTYkSGD5mTnho3yk n3yKqf2+L4o4DcsCp/9AsAAo6TKqa+BPk7XpNVa+ED5ck+6RumdhZ/+0UtKoujWX HussnIc4ffLoOS25Ppk9CfZF+KAhOwzz1x6aT3UjvvnoVx/6WRFhEpDZFWCr9bXw bBNhuYNwy5jjJ9OHD9pqhdTCT1aRIngQR8acR3OADtXErt+tHkW+t8O4WAJPrXuP ajNjm84Ygr6NEGraHyMeKbJkDJ5Ydj411vBx8cyZ/4bP2/fh/+BVZCFX8cOodBw/ s0JxQUZ35FriKAkdxTPb4PvPiO/x7N4CLPJqyJSsCEZY0YMngkUdfamspF0K10Ap j8vQUMGYAmSK4NMOVBHygLf5zVqDoM14YRlX1vXHPklMQT8bobgDnSdcb0f015kU 6ztLcs0eZrn+jzQbwI5csro2k9mbezT/M4gbdOiF77cNAH+XBPo= =bhAu -----END PGP SIGNATURE----- --jvcljfmy4ffojfpo--