Español

Field manual · SQL traps

Time zones: store UTC, show local

Grouping by day without deciding the time zone splits the same session across two days, and a clock change turns one day into 23 hours and the next into 25.

Where it shows up

Any timestamped data that crosses time zones or a clock change — almost every system with users in more than one zone.

Example

Wrong: date_trunc with no zone decided

SELECT date_trunc('day', moment_utc) AS day
FROM sessions
GROUP BY day;
-- moment_utc carries no zone: date_trunc cuts on the UTC day.
-- In Mexico City (UTC−6) everything after 6 p.m.
-- lands on the next day

Right: both conversions, in that order

SELECT date_trunc(
  'day',
  moment_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/Mexico_City'
) AS local_day
FROM sessions
GROUP BY local_day;

Both conversions are needed, in that order: the first says which instant that zone-less stored TIMESTAMP represents (read it as UTC), the second moves it to the clock over there. With only one conversion, the engine returns an instant and date_trunc cuts using the machine's own zone — the same data gives a different day on every computer, and a clock change splits the same night's session across two dates.

This trap — and four more — is hiding inside a real case file, with real data.

Play the first case free →

Other traps