Timezones, daylight saving jumps, irregular intervals, and silent gaps make the timestamp column the most error-prone field in real data. Convert instants across zones, walk through the hour that does not exist, and watch resampling and gap-filling change the story the same data tells.
The one rule: store UTC, convert at display
A timestamp names a physical instant. A wall-clock reading like "02:30" names a position on somebody's local clock, and that mapping changes with politics, geography, and the season. The only safe pattern is to store the instant once, in UTC, and apply a timezone only when a human needs to read it. Every bug in this guide comes from breaking that rule.
How this playground stays honest: all timestamps are hardcoded constants in milliseconds since the Unix epoch, timezone and DST rules are written out as a small data table (not read from your browser), and the reading series is generated once by a seeded generator (mulberry32, seed 20240325). Every number on this page is recomputed live from those constants with plain arithmetic.
1 · One instant, four wall clocks
Six order events, stored once in UTC. Switch the display zone and watch the same instants land on different dates, shift across midnight, and (in the DST zone) skip or repeat an hour. The data never changes; only the lens does. Switch at least twice to see it move.
| Event | Stored (UTC) | Shown in UTC | Offset | Day shift |
|---|---|---|---|---|
| Order placed | Sat Mar 30, 12:00 | Sat Mar 30, 12:00 | +00:00 | same day |
| Payment captured | Sat Mar 30, 23:45 | Sat Mar 30, 23:45 | +00:00 | same day |
| Shipment scanned | Sun Mar 31, 00:30 | Sun Mar 31, 00:30 | +00:00 | same day |
| Out for delivery | Sun Mar 31, 01:30 | Sun Mar 31, 01:30 | +00:00 | same day |
| Support ticket opened | Sun Oct 27, 00:30 | Sun Oct 27, 00:30 | +00:00 | same day |
| Ticket escalated | Sun Oct 27, 01:15 | Sun Oct 27, 01:15 | +00:00 | same day |
The timezone rules used above, spelled out as data
Nothing here asks your browser what time it is or what zone you are in. Each local time is computed as UTC + offset(instant), with the offsets defined in this table, so the rendering is identical everywhere and every row can be checked by hand.
2 · The missing hour and the double hour
Twice a year the Central European wall clock breaks its promise to be a number line. Step the cursor across both 2024 transitions: in March, 02:00 to 02:59 never happens; in October, it happens twice. UTC underneath ticks evenly through both.
UTC
22:00
UTC
23:00
UTC
00:00
UTC
01:00
UTC
02:00
UTC
03:00
UTC
04:00
CET
23:00
CET
00:00
CET
01:00
CEST
03:00
CEST
04:00
CEST
05:00
CEST
06:00
Local labels read 01:00 then 03:00: the wall clock never shows 02:xx on Mar 31. The UTC row underneath stays perfectly even.
UTC 22:00 -> local 23:00 (UTC+01:00)
The bug this causes: subtracting wall-clock times
Take two real events from the timeline above: shipment scanned and out for delivery. In local Central European wall-clock time they read Sun Mar 31 01:30 and Sun Mar 31 03:30. Subtract the UTC instants and you get the truth. Subtract the local clock readings, the way naive code does, and you get the lie:
True elapsed (UTC arithmetic)
1h 00m
Naive local subtraction
2h 00m
double-counts the skipped hour: off by +60 min
3 · Resampling is an aggregation policy
Real-world readings rarely arrive on a clean grid. This series has 335 measurements taken every 25 to 95 minutes over two weeks, with a 4-hour traffic incident on Mar 29. Resample it hourly, daily, and weekly, and decide which story you would have told your team.
Peak hourly mean
1021
requests per minute
Peak daily mean
327
requests per minute
Peak weekly mean
238
requests per minute
All three views aggregate the same 335 raw readings; only the bucket width changes. The 4-hour incident on Mar 29 reads as a 1021-high emergency hourly, a mild 327 bump daily, and vanishes into a 238 weekly average. None of these charts is wrong. Each one answers a different question, which is why the granularity of a resample is an aggregation policy you choose, not a detail.
Also note: on the hourly grid, 49 of 336 slots are empty, because readings arrive every 25 to 95 minutes. Resampling finer than your real sampling cadence does not create information; it creates gaps. Buckets here are aligned to UTC; bucketing by a local calendar would shift every daily boundary by that zone's offset and change the daily numbers.
4 · Gaps: missing data wearing a timestamp
Now break the collector. Two outage windows drop every reading they cover, and the 6-hour resample grid suddenly has empty buckets. Something must go in those slots, and each choice quietly asserts a different claim about the world. Compare at least two policies with the outages on.
Toggle the outages to drop readings from two windows, then compare fill policies.
Leave missing
-
inject the outages to compute
Forward-fill
-
inject the outages to compute
Zero-fill
-
inject the outages to compute
There is no neutral option. Leaving NaN is honest but breaks downstream code that cannot handle missing values. Forward-fill is fine for slow-moving state (a thermostat setting) and wrong for flows like request rates. Zero-fill is correct only when absence really means zero, for example "no sales recorded" in a complete ledger. The failure mode is not picking the wrong policy; it is letting a library default pick it for you silently.