Re: Timezone handling with timestamp without time zone columns

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.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"