SQL, data modeling, data quality, EDA, visualization · insight-0054

Store instants in UTC and assign the business date with the local zone of where it happened

Situation
Bookings from several countries are aggregated to daily totals using the server's clock or a UTC date.
Action
Store event instants as timezone-aware UTC. Derive the business date with the relevant zone (origin airport, store or customer) using full IANA zone names, and store that date as its own column used for grouping.
Reason
A UTC instant is unambiguous, but "which day" depends on the observer; making the zone explicit makes daily metrics reproducible.
Boundary
Multi-region sales, flight operations, call centres and any daily or hourly KPI.

Symptom or risk

Late-evening local bookings land on the next day, daily patterns shift by hours, and day-of-week features become wrong for distant markets.

Single-zone businesses can use local time throughout if the zone is documented and DST is handled.

Extra columns and conversion logic in exchange for unambiguous daily numbers.

Using a timestamp type without time zone, which silently ignores offsets in the input.

PostgreSQL stores timestamp with time zone internally in UTC and converts to the session time zone for display; timestamp without time zone silently ignores any zone in the input.
Date/Time Types (PostgreSQL 18 documentation, section 8.5)