SQL, data modeling, data quality, EDA, visualization · insight-1055
Store the zone name for a future local event, not the offset you know today
- Evidence type
- documented fact
- Difficulty
- hard
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, logistics, cross-industry
Summary: situation, action, reason, boundary
Situation
A schedule table stores departure times for next year as timestamps with a fixed offset computed at load time, and a government changes its daylight-saving rule.
Action
Store future local events as local date-time plus an IANA zone name, resolve to UTC at read time with the current tz database, and refresh the database on a schedule. Keep the resolved UTC value as a derived column that can be recomputed.
Reason
Rules are legislation and change with little notice; a zone name lets the resolution follow the new rule, an offset cannot.
Boundary
Flight schedules, appointments, shifts, reminders and recurring events.
Symptom or risk
A fixed offset hard-codes today's rules, so after a rule change every stored future instant is wrong by an hour while looking valid.
Exceptions
Past events, where the actual instant is known and UTC is sufficient.
Trade-offs
Reads need the database and update discipline across services and containers.
Pitfalls
Using three-letter abbreviations such as EST, which are ambiguous and not unique.
Lessons and concepts
Claims and their sources
The IANA time zone database is maintained because governments change time-zone and DST rules with little notice, and region names are preferred over ambiguous abbreviations as identifiers.
In PostgreSQL full zone names carry daylight-saving rules while abbreviations such as EST are fixed offsets.