SQL, data modeling, data quality, EDA, visualization · insight-1054
Add calendar days and elapsed hours with different interval types
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, logistics, cross-industry
Summary: situation, action, reason, boundary
Situation
A feature computes "same time next day" with + interval '24 hours', and on the day clocks change the result is off by an hour from the local departure schedule.
Action
Use calendar intervals (interval '1 day' on a timestamptz in the business zone) when you mean the same wall-clock time tomorrow, and elapsed intervals (24 hours) when you mean duration. Test with dates around a transition in a DST zone.
Reason
In a DST zone a calendar day is not always 24 hours; the types encode which meaning you want.
Boundary
Schedules, rolling windows by calendar day, subscription renewals and lag features on local time.
Symptom or risk
Elapsed-time arithmetic and calendar arithmetic differ across a daylight-saving change, so rows near the transition are shifted and joins to a schedule keyed by local time miss.
Exceptions
Zones without DST, such as Europe/Istanbul since 2016, where both give the same result.
Trade-offs
Engines differ in how intervals interact with zones, so portable code needs a test.
Pitfalls
Doing the arithmetic on UTC timestamps and then formatting in local time.
Example
The PostgreSQL manual's America/Denver case: adding 1 day to 2005-04-02 12:00 gives 2005-04-03 12:00 local, while adding 24 hours gives 13:00 local.
Lessons and concepts
Claims and their sources
In PostgreSQL adding interval '1 day' to a timestamptz keeps the local time of day across a DST change, while interval '24 hours' adds elapsed hours.