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