Time zones in reporting: how to stop them breaking one report a quarter

Time zone bugs are the ones that never quite reproduce. A report is off by a little, a scheduled send arrives an hour early, a daily total does not match the shop's own dashboard. Each is small and each erodes trust in every other number on the screen.

The whole class is avoided by two rules applied without exception, and broken by the one operation that looks harmless.

The two rules

1. Store every timestamp in UTC. Always, without exception, including anything that arrives from an external system. An instant is an instant.
2. Convert at the edges only — when rendering for a person, and when deciding what "today" means for a shop.

Between those two points, everything reasons in UTC. The bugs appear when a conversion sneaks into the middle: a query grouping by a local date against UTC-stored data, or a value converted twice.

Whose day is it anyway

This is the decision most systems never make explicitly, and it matters whenever a shop is in a different zone from the server or from the person reading.

• Business reporting — use the shop's own time zone, read from the shop rather than configured by hand. A daily revenue figure should match what the merchant sees in WooCommerce.
• Operational dashboards — the viewer's local time is often more useful, as long as the screen says which.
• Never mix them in one screen without labelling, which is how somebody concludes a number is wrong when it is merely different.

Scheduling is where it costs real money

"Send at 9 a.m." means nine in the morning where the recipient is, or where the shop is — not 09:00 UTC.

1. Store the intended local time and the zone, not a pre-computed UTC instant. Otherwise a daylight-saving change makes a recurring send drift by an hour.
2. Compute the actual send moment at dispatch, from the stored local time and the zone as it is then.
3. Quiet hours must use the recipient's plausible zone, which matters most for SMS, where several countries restrict marketing by time of day.
4. Cancellation windows use server time, never the browser's. A clock a user can change is not a clock you can enforce a deadline with.

The two days a year that break things

Daylight saving produces one day with 23 hours and one with 25, and both create real cases.

• The missing hour. A send scheduled for 02:30 on the spring change does not exist. Decide what happens — send at the next valid moment — rather than discovering it.
• The repeated hour. In autumn, 02:30 happens twice. A job keyed on local time can run twice, which for a send is a duplicate to every recipient.
• Recurring schedules drift if stored as UTC instants. This is why the rule above says store the local time and the zone.
• Different countries change on different dates, so a multi-shop account will have a fortnight where its shops are offset differently than usual.

Times that arrive from somewhere else

External systems are the most common source of a silent offset, and the failure is invisible because everything still looks like a date.

1. WooCommerce stores both local and GMT columns. Use the GMT ones, and know which you took.
2. MySQL's UNIX_TIMESTAMP() interprets a datetime in the session time zone, so on a server not running in UTC it shifts every value by the offset. Convert in application code instead. This one produces checksums that disagree for ever while every individual record looks correct.
3. Never infer a zone from an offset. +01:00 is several countries with different daylight-saving rules; store the zone name.
4. Pin it with a test that runs with the process in a non-UTC zone, because every one of these passes when your laptop happens to be in UTC.