SQL, data modeling, data quality, EDA, visualization · insight-1053
Truncate timestamps in an explicit time zone, because date_trunc on a timestamptz uses the session zone
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A daily booking count is built with date_trunc('day', created_at), and the same query returns different daily totals when run from a notebook and from a scheduled job.
Action
Pass the zone explicitly (PostgreSQL 12 or later: date_trunc('day', created_at, 'Europe/Istanbul')) or convert with AT TIME ZONE before truncating, and make the business zone a named parameter. Add a test that the bucket for a known instant is the same in every environment.
Reason
The same instant has different calendar dates in different zones; only an explicit zone makes the bucketing reproducible.
Boundary
Daily and weekly aggregates, cohort dates and scheduled reports across environments.
Symptom or risk
For timestamp with time zone the day boundary depends on the session TimeZone setting, so two environments cut days at different instants and totals shift by hours of traffic.
Exceptions
timestamp without time zone columns, which are taken at face value and need a documented meaning.
Trade-offs
Passing zones adds clutter; a calendar table keyed by local date can hide it.
Pitfalls
Setting the zone in one connection and forgetting that a pooled connection resets it.
Lessons and concepts
Claims and their sources
In PostgreSQL date_trunc on a timestamptz truncates in the session time zone unless a zone is supplied as a third argument.