FAILURE MODES

Delivered 42 Minutes Before It Was Placed

On Sunday, Nov 1, 2026, US clocks go back and 1 AM happens twice. We replayed that night in PostgreSQL 18.4: plain local timestamps gave 47 of 2,940 delivery durations wrong, worst minus 42 minutes. Storing the instant gave 0 wrong.

2026-10-26

This Sunday, one of our test orders arrives 42 minutes before it was placed. On paper.

US clocks go back on Sunday, Nov 1, 2026: at 2:00 AM Central the wall clock returns to 1:00 AM, so 1:00 to 1:59 AM happens twice. We replayed that night in PostgreSQL 18.4 on one laptop, our own test, US Central, America/Chicago.

The 40-second reel: delivered 42 minutes before it was placed.

Our settings

One event a second from Oct 31 00:00 to Nov 2 00:00 local time: 176,400 events in 49 real hours, because Nov 1 has 25. One order a minute, delivery 10 to 90 minutes. That gives 2,940 orders to measure durations against.

What plain local time did

Stored as plain local time, timestamp without time zone, GROUP BY hour gives 48 buckets, and the 1 AM bucket holds 7,200 events: two real hours of 3,600 merged into one. Durations computed from local times came out wrong for 47 of 2,940 orders, 16 of them negative. The worst was order 1555: placed 1:54 AM CDT, delivered 18 minutes later at 1:12 AM CST, computed as minus 42 minutes.

The fix

Store the instant, timestamptz, and group with the three-argument date_trunc:

SELECT date_trunc('hour', ts,
         'America/Chicago') AS hr, count(*)
FROM events GROUP BY hr ORDER BY hr;

That gives 49 buckets, 3,600 in every one, and 0 wrong durations. The two 1 AM hours stay apart, one labeled CDT and one CST.

The trap

One trap we measured: converting with ts AT TIME ZONE ‘America/Chicago’ before the GROUP BY merges the two 1 AMs again, back to 7,200 in one bucket. Storing the instant does not help if you convert it back to a wall clock before you group.

Not a Postgres bug

timestamp without time zone is documented as “not adjusted for time zone”, and timestamptz is “stored internally as UTC”. The daily total of a plain local log is still right: 90,000 events on Nov 1. Hourly buckets, durations, and unique keys are where it breaks. The three-argument date_trunc needs PostgreSQL 12 or later. US only; Europe changed on Oct 25.

What we did not verify

This was our own test on synthetic data, replaying the Nov 1, 2026 fallback night in PostgreSQL 18.4 on one laptop, US Central. The counts depend on the settings we chose: one event a second, one order a minute, delivery 10 to 90 minutes. It is a small test, not a benchmark, and it says nothing about other time zones or other databases. The script was written with an AI model made by Anthropic, and the voiceover and visuals in the reel are AI-generated. The numbers are from our own run. Watch the reel on Threads.

This post started as an Instagram post →

Discussion

Talk it through

Argue with us on Instagram.