The same moment, three dates

There is no such thing as daily revenue. There is revenue bucketed by a calendar day in a specific time zone, and the zone is usually chosen by whoever wrote the first query. Pick a moment and drag it across midnight below. The instant never moves. The date does, once per zone.

What the strip is showing

Every date in that graphic is computed in your browser with Intl.DateTimeFormat and a real IANA zone name. No library, no offset table I typed in. The same code ran under Node at build time to produce the numbers in this post.

The five default zones span 17 hours, so at 23:30 UTC on Monday 14 September they produce two dates: Monday in Los Angeles and UTC, Tuesday in London, Kolkata and Sydney. Push the ends further apart and you get three. At 10:30 UTC that same day it is still Sunday 13 September in Pago Pago, Monday in London, and already Tuesday 15 September in Kiritimati. Three dates, one timestamp, all real places with real customers.

Two Mondays that never reconcile

The second scene is the one that costs money. “Monday” for a team reporting in Los Angeles runs from 07:00 UTC Monday to 07:00 UTC Tuesday. “Monday” for a team reporting in London runs from 23:00 UTC Sunday to 23:00 UTC Monday. The two windows overlap for 16 hours and disagree for 8.

With the illustrative orders in the graphic, the Los Angeles Monday holds 11 orders worth $792 and the London Monday holds 13 orders worth $884. Same table, same filter on order_date = '2026-09-14', two answers. Neither team is wrong. They are answering different questions and calling them the same thing.

The orders are made up. The 8-hour disagreement is not. It is the offset between the two zones, and it applies to every order that lands in it.

Days are not 24 hours

Scene three switches the window to 8 March 2026 in Los Angeles. Local clocks jump from 01:59 to 03:00, so that Sunday has 23 hours. On 1 November the hour repeats and the day has 25. I computed both by finding the two local midnights with Intl and subtracting, which is also the only honest way to compute it: any formula with a hard-coded offset gets one of the two days wrong.

“Average hourly revenue” divided by 24 is wrong on both days. A pipeline that generates hourly partitions by adding 3,600 seconds produces a partition that does not exist in March and two that collide in November. Rolling 7-day windows built from local midnights are 167 or 169 hours wide once a year each. London does the same on 29 March and 25 October. Sydney does it in the other direction, in October and April.

Half hours and quarter hours

Kolkata is +05:30. Kathmandu is +05:45. Eucla, a strip of Western Australia that keeps its own offset, is +08:45. If your hourly chart is bucketed by UTC hour and your reader is in any of these, every bar straddles two of their hours. The 10:00 to 11:00 UTC bar is 15:30 to 16:30 in Kolkata. There is no local hour it belongs to.

The fix is the same as for days: bucket at read time, in the reader’s zone, from the UTC instant. DATE_TRUNC('hour', CONVERT_TIMEZONE('Asia/Kolkata', ts_utc)) gives you bars that start on the half hour, which is what a person in Kolkata expects to see.

How to audit an existing table

Most warehouses I have opened have at least one TIMESTAMP_NTZ column that was written by an application server in its own local time, then loaded as if it were UTC. You cannot prove that from the column alone, but you can find the fingerprints: a daily peak in the writer’s business hours, and a missing or doubled hour on DST days.

-- Which hour of the day does activity peak in, and is that plausible for UTC?
WITH hourly AS (
  SELECT EXTRACT(HOUR FROM created_at) AS hr, COUNT(*) AS n
  FROM orders
  GROUP BY 1
)
SELECT hr, n, ROUND(100 * n / SUM(n) OVER (), 1) AS pct
FROM hourly
ORDER BY n DESC
LIMIT 5;

-- Rows stamped inside the hour that did not exist in America/Los_Angeles on 2026-03-08.
-- If created_at were really UTC, this hour is ordinary and should be populated like its neighbours.
SELECT COUNT(*) AS rows_in_missing_hour
FROM orders
WHERE created_at >= '2026-03-08 02:00:00' AND created_at < '2026-03-08 03:00:00';

If the peak hour is 17:00 and the writer is in Los Angeles, the column is probably UTC, because 17:00 UTC is 10:00 local. If the peak is 10:00 and the skipped hour is empty while the hours around it are busy, the column is local time wearing a UTC label. Fix the loader, backfill the old rows with CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', created_at), and write down the cutover timestamp where the next person will find it.

What to do

• Store instants as TIMESTAMP_TZ or UTC TIMESTAMP plus a report_tz column holding an IANA name. Never an abbreviation, never a numeric offset.
• Put the day bucketing in one place, a dbt macro or a semantic layer measure, and make every “daily” model call it.
• Test the DST days explicitly. Assert that 8 March in Los Angeles has 23 hours of data and 1 November has 25.
• Run the audit query above on every TIMESTAMP_NTZ column that came from an application. Assume local time until proven otherwise.
• When two teams disagree on a daily number, ask which midnight they used before you ask anything else.