Unit 08.03: Timezones and the shifting daily total

Unit ID: SQL-M08-U04 - Estimated active time: 13-16 minutes Objective: explain why the same data produces different daily totals, and record the timezone you used.

The same instant belongs to different days

An order at 2026-06-30 21:00 UTC is on 1 July in India (UTC+5:30) and still 30 June in London. Nothing about the data changed - only the timezone the reader is standing in.

That means "orders on 30 June" is not a well-defined question until someone names a timezone.

Where it actually bites

Our placed_at column is a plain TIMESTAMP with no zone attached:

SELECT MIN(placed_at), MAX(placed_at) FROM orders;
-- 2026-06-01 10:00:00 | 2026-06-30 21:00:00

The values carry no offset, so the database cannot convert them and will not warn you. Every daily aggregate silently assumes whatever timezone the data was recorded in - an assumption that lives in someone's head rather than in the schema.

Watching a total move

Converting before truncating shifts rows across day boundaries:

SELECT DATE_TRUNC('day', placed_at) AS day_as_stored,
       COUNT(*) AS orders
FROM orders
WHERE placed_at >= '2026-06-30' AND placed_at < '2026-07-01'
GROUP BY day_as_stored;
-- 2026-06-30 | 33

Late-evening orders are exactly the ones that move when a conversion is applied. A business whose peak is in the evening will see meaningfully different daily numbers depending on the timezone chosen - and the month-end boundary is where it matters most, because that is what gets reported.

The practical rules

Store UTC, convert for display. Mixed local times in one column are unrecoverable later.
Use timezone-aware types where the engine has them (TIMESTAMPTZ), so the offset is data rather

than folklore.

State the timezone in the evidence note. "Daily totals in Asia/Kolkata" removes an entire class of

dispute.

Beware DST. In zones that observe it, one day a year has 23 hours and another has 25. India does

not, which is convenient - but a global report will include zones that do.

Practice

A colleague's daily revenue does not match the finance system's by a small amount, only on month-end days. What is your first hypothesis?

Check your answer

A timezone difference. Month-end is precisely where a few hours' offset moves rows between reporting periods, and only the boundary days are affected - which matches the symptom exactly.

Ask which timezone each system truncates in before investigating anything else.

Takeaway

A daily total is only defined once a timezone is. Store UTC, convert deliberately, and write the timezone into the evidence note.

---