Pitfall
Convert, then truncate
Cutting a UTC time into days, hours or months gives UTC days: convert to the reader's time zone first.
ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' gives the local time; only then CAST(… AS DATE), date_trunc or
EXTRACT(HOUR …), and write the date filter in local time too. Days are not all 24 hours: the day the clocks go back lasts
25, the day they go forward 23. Store in UTC, report in the reader's time zone, and say which.
WITH events(occurred_at) AS (VALUES (TIMESTAMP '2026-06-02 02:30:00'), (TIMESTAMP '2026-06-02 14:00:00'))
SELECT occurred_at,
CAST(occurred_at AS DATE) AS utc_day,
CAST(occurred_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS DATE) AS new_york_day
FROM events
ORDER BY occurred_at;
| occurred_at | utc_day | new_york_day |
|---|---|---|
| 2026-06-02 02:30:00 | 2026-06-02 | 2026-06-01 |
| 2026-06-02 14:00:00 | 2026-06-02 | 2026-06-02 |