SQL, data modeling, data quality, EDA, visualization · insight-0055
Expect 23-hour and 25-hour days in hourly data around daylight-saving changes
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, ML engineer, Data scientist
- Industries
- energy, telecom, aviation
Summary: situation, action, reason, boundary
Situation
An hourly demand or call-volume series in local time shows a missing hour in spring and a doubled hour in autumn.
Action
Keep the hourly series in UTC for modelling and joins, convert to local time only for display and day-level grouping, and use zone names (Europe/Berlin, America/New_York) rather than fixed abbreviations. Add a DST-day test to the pipeline.
Reason
Zone names carry daylight-saving rules while abbreviations are fixed offsets; local wall-clock hours are not unique around transitions.
Boundary
Energy load, call centres, web traffic and any hourly local-time series.
Symptom or risk
Hourly joins drop or duplicate rows, daily sums jump by about 4%, and seasonal models learn a spurious pattern on two days a year.
Exceptions
Zones without daylight saving, such as Europe/Istanbul (fixed UTC+3 since 2016); check the zone's current rules rather than assuming.
Trade-offs
UTC-based analysis is less intuitive for local operations teams; show both.
Pitfalls
Generating a local-time hourly calendar with 24 rows for every day.
Lessons and concepts
Claims and their sources
A full time zone name implies a set of daylight-saving rules, whereas an abbreviation such as EST is a fixed UTC offset.