Level 6 · Black Friday in the ad platform's time zone

Two systems can count the same orders and still disagree every single day. If one counts by Eastern days and the other by UTC days, the evening orders land on different dates.

Local time versus UTC

Harbor & Pine stores order_date in store local time, US Eastern, as a plain TIMESTAMP with no time zone attached. Ad platforms usually report in UTC. In late November, Eastern Standard Time is UTC-5: an order at 21:30 on Friday, Nov 28 in New York is 02:30 on Saturday, Nov 29 in UTC.

Orders peak in the evening, so this is not a rounding issue. From 19:00 to midnight on Black Friday alone, 18 orders move to the next UTC day.

What the starter does

It labels local days as utc_day, and filters on local time. Compare the two versions:

utc_day	starter (local days)	fixed (UTC days)
2025-11-28	56	43
2025-11-29	51	55
2025-11-30	58	59
2025-12-01	67	53

The UTC version of Nov 28 runs from 19:00 on Nov 27 to 19:00 on Nov 28 local time.

The fix

Convert once, then use the converted value everywhere:

CAST(order_date + INTERVAL 5 HOUR AS DATE) AS utc_day
...
WHERE order_date + INTERVAL 5 HOUR >= '2025-11-28'
  AND order_date + INTERVAL 5 HOUR < '2025-12-02'

A fixed offset is correct here because the whole window falls in standard time: daylight saving time ended on Nov 2, 2025. Over a range that crosses a DST change, a fixed offset is wrong for part of it (Eastern is UTC-4 in summer), and you should use a real time zone conversion such as AT TIME ZONE 'America/New_York' in Postgres or Snowflake’s CONVERT_TIMEZONE, which know the DST calendar.

Common mistakes

• Shifting the label but not the filter: the window still starts and ends at local midnight, so the first and last UTC days are incomplete.
• Subtracting instead of adding. UTC is ahead of US time zones.
• Assuming a column’s time zone from its name. Check the data documentation or ask the engineer who owns the pipeline.

In SQL Server

SQL Server 2016+ converts with Windows zone names, which handle DST:

SELECT CAST(order_date AT TIME ZONE 'Eastern Standard Time'
                       AT TIME ZONE 'UTC' AS date) AS utc_day
FROM orders;

The first AT TIME ZONE says what the stored value is; the second converts it. DATEADD(hour, 5, order_date) is the fixed-offset version.

On the job

Ad spend, web analytics and payment systems often each use a different clock. State the time zone next to every daily number you publish.