public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: Timezone handling with timestamp without time zone columns
Date: Sat, 28 Feb 2026 13:00:54 +0100
Message-ID: <7eb7di2cb63iryenre6vc5bzqswpbna5rriazrn4zquppj5zqb@4whytmyvvmet> (raw)
In-Reply-To: <[email protected]>
References: <YT4P288MB01507D63F23F6B505AEB759E8375A@YT4P288MB0150.CANP288.PROD.OUTLOOK.COM>
<[email protected]>
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
>
> - use "timestamp with time zone", store timestamps with time zone
> and make sure that the parameter "timezone" is set correctly in each
> database session
>
> or
>
> - 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=> set timezone = 'Canada/Pacific';
SET
hjp=> select '2025-11-02 08:23Z'::timestamptz::timestamp;
╔═════════════════════╗
║ timestamp ║
╟─────────────────────╢
║ 2025-11-02 01:23:00 ║
╚═════════════════════╝
(1 row)
hjp=> select '2025-11-02 09:23Z'::timestamptz::timestamp;
╔═════════════════════╗
║ timestamp ║
╟─────────────────────╢
║ 2025-11-02 01:23:00 ║
╚═════════════════════╝
(1 row)
(OTOH sometimes you need the local time without any reference to a time
zone or at least without a UTC offset)
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Timezone handling with timestamp without time zone columns
In-Reply-To: <7eb7di2cb63iryenre6vc5bzqswpbna5rriazrn4zquppj5zqb@4whytmyvvmet>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox