DST and the Missing Hour

Concepts covered: Timezones, Date Differences, Date Math, Timestamp Types, Grouping

Daylight saving arrives disguised as an anomaly: "Sunday revenue jumped in November and nothing launched", or "the hourly chart has a hole at 2 a.m. every March". Both come from the same fact. In a zone that observes daylight saving, 2 local days a year are not 24 hours long, and any math that assumes a fixed-length day or a unique hour label breaks on those days.

• 23 hours
• 25 hours
• 4.2%

The hour that happens twice

Group that day by local hour and the 01:00 bucket holds 2 real hours of orders, so it reads as a spike. Group the March day the same way and the 02:00 bucket never appears, because 02:00 to 02:59 did not happen on any clock in the zone. The daily total moves too: the long day carries an extra hour of sales into a day-over-day comparison, which is the November jump with nothing launched.

A table that stores naive local time loses information on these days. The wall-clock value 2024-11-03 01:30 names 2 instants an hour apart, and 2024-03-10 02:30 names none. Each engine resolves them by its own rule, picking one of the 2 instants or pushing the missing time forward, so no query can recover what the writer threw away.

Calendar days versus elapsed hours

Interval math splits into 2 kinds that agree on every other day of the year. In Postgres, adding INTERVAL '1 day' to a TIMESTAMP WITH TIME ZONE keeps the wall-clock time in the session's zone, while INTERVAL '24 hours' adds 24 elapsed hours. Across the spring change they land an hour apart.

SET TIME ZONE 'America/Los_Angeles';

SELECT TIMESTAMPTZ '2024-03-09 12:00' + INTERVAL '1 day'    AS plus_1_day,
       TIMESTAMPTZ '2024-03-09 12:00' + INTERVAL '24 hours' AS plus_24_hours;

-- plus_1_day:    2024-03-10 12:00:00-07  (23 hours elapsed)
-- plus_24_hours: 2024-03-10 13:00:00-07  (24 hours elapsed)

Durations go wrong the same way when computed from naive local values. A support ticket opened at 00:30 and closed at 05:30 local time on 2024-03-10 shows 5 hours, but 4 hours passed; the same ticket on 2024-11-03 shows 5 hours when 6 passed. Any SLA threshold near those values gets the wrong verdict twice a year. Subtract instants instead, whether TIMESTAMP WITH TIME ZONE values or UTC, and the elapsed time comes out right every day.

-- Real length of each local day in one zone (Postgres)
SELECT CAST(d AS DATE) AS local_day,
       EXTRACT(EPOCH FROM
           (d + INTERVAL '1 day') AT TIME ZONE 'America/Los_Angeles'
         - d AT TIME ZONE 'America/Los_Angeles'
       ) / 3600 AS hours_in_day
FROM generate_series(TIMESTAMP '2024-11-01', TIMESTAMP '2024-11-05',
                     INTERVAL '1 day') AS g(d);
-- 2024-11-03 returns 25; the other days return 24

Joining hours_in_day onto a daily revenue table turns the November question into a sentence: divide by it, compare revenue per hour, and the jump disappears. The interviewer is listening for that move, because it keeps the local day finance asked for and removes the clock from the comparison.

Answers that hold up

• Measure every duration by subtracting instants (TIMESTAMPTZ or UTC), never naive local wall-clock values
• Build hourly buckets on UTC, where every hour is 60 minutes, then label each with its local time and offset
• Keep daily totals on the local day, carry hours_in_day, and compare per-hour rates on the 2 DST days
• Use calendar intervals for "same time tomorrow" and elapsed ones for "24 hours later", and say which you mean

Bucketing on UTC gives November 3 its 25 rows, with 2 rows labelled 01:00 and told apart by offset, and gives March 10 its 23. Every bar covers the same length of time, which is the property an hourly chart needs to be read at all.

About this lesson

This section is part of the Date Arithmetic: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.