Python and Postgres Don’t Agree on What “CET” Means

We were paged for a timezone. A user had created a scheduled freeze through our API with its timezone set to localtime, and the API accepted it with a 201. From then on, every pull request on that repository failed evaluation. The engine retried those pull requests, and each failure was a Sentry event, one every 12 seconds. Our engine error-rate alert fires at 300 errors an hour; in the hour it fired, that one row accounted for 313 of about 344.

Python knows localtime and Postgres doesn’t. Working out why turned up a second problem that never raises an error: for four timezone names, Python and Postgres disagree by an hour every summer. Both came from the same place: two programs reading one column against two different timezone databases.

One row, two timezone databases

A scheduled freeze stops a repository’s merges for a time window. We store its start and end as naive local times (2026-10-01 09:00) and a timezone column that says which zone that 09:00 is in. Two different programs then read the same row:

flowchart LR
    API["Scheduled-freeze API<br/>validates with zoneinfo.available_timezones()"] --> ROW[("scheduled_freeze<br/>start, end: naive<br/>timezone: text")]
    ROW --> PG["Postgres<br/>start / end AT TIME ZONE timezone<br/>which freezes to load, which to delete"]
    ROW --> PY["Python zoneinfo<br/>current-datetime condition<br/>is this PR frozen right now"]
    PG -.reads.-> PGTZ["Cloud SQL's tz files<br/>+ abbreviation table"]
    PY -.reads.-> PYTZ["engine image's /usr/share/zoneinfo<br/>(Debian trixie + tzdata-legacy)"]

Postgres decides which freezes matter now. The query that loads a repository’s active and pending freezes filters on "end" AT TIME ZONE timezone > now(), and the one that loads only active freezes also filters on start AT TIME ZONE timezone <= now(). A cleaner prunes ended freezes with one DELETE ... WHERE "end" AT TIME ZONE timezone <= now() over the whole table. Python evaluates the freeze itself, as a current-datetime condition with the zone attached.

The API validated the name against zoneinfo.available_timezones(), which is Python’s view only. Each side reads its own copy of the tz files, and Postgres adds its own abbreviation table on top. The engine image runs Debian trixie with tzdata-legacy, which gives Python 599 names. Production Postgres, on Cloud SQL, knows 597.

The two names Python has and production Postgres refuses are localtime, which Debian ships as a symlink to /etc/localtime, and Factory, a placeholder zone in the IANA data. Postgres builds don’t agree with each other on these either: Cloud SQL and Homebrew’s Postgres 18 refuse localtime, while our test Docker image accepts it. Homebrew’s Postgres 18 also accepts Factory. A freeze stored as localtime made the loading query fail for every pull request on its repository, and made the cleaner’s DELETE fail for every organization, because it runs over the whole table.

The other four names, CET, EET, MET and WET, convert without error on both sides, but the results differ every summer.

In Python, CET is a zone with summer time. Postgres has CET as a zone too, but AT TIME ZONE looks a name up in its abbreviation table first, and there CET is a fixed +01:00 with no summer time. Noon on July 15, on a stock Postgres 18 (production’s Cloud SQL gives the same answer):

SET timezone = 'UTC';
SELECT timestamp '2024-07-15 12:00' AT TIME ZONE 'CET';          -- 2024-07-15 11:00:00+00
SELECT timestamp '2024-07-15 12:00' AT TIME ZONE 'Europe/Paris'; -- 2024-07-15 10:00:00+00

>>> from datetime import datetime, timezone
>>> from zoneinfo import ZoneInfo
>>> datetime(2024, 7, 15, 12, tzinfo=ZoneInfo("CET")).astimezone(timezone.utc)
datetime.datetime(2024, 7, 15, 10, 0, tzinfo=datetime.timezone.utc)

From late March to late October, a freeze in CET would mean one instant to the Python condition and another to the Postgres queries. Postgres puts both ends of the window an hour late. The query the merge queue uses to deprioritize frozen pull requests would miss the freeze for its first hour, while merge protections would already treat the repository as frozen. It would then keep deprioritizing for an hour after the freeze ended. The cleaner would delete it an hour late, with no error and no log line. No stored freeze used any of the four names when we checked, so this one never bit anyone.

Both are real bugs. We just found the localtime one first.

Two fixes I threw away

The first fix asked Postgres. Before storing a freeze, the API ran SELECT now() AT TIME ZONE :tz, the same operation every freeze query does, and returned a 422 if Postgres refused. That meant a database round-trip on every create and update, so the next step was a per-process cache.

The second fix went the other way: stop letting Postgres read the timezone at all. The queries would narrow the rows using bounds that hold in any zone (UTC offsets run from -12:00 to +14:00), and Python would make the final call on each row using the freeze’s own zone. I built that PR and closed it without merging.

Both were wrong because neither made the two sides share one timezone list. The first asked Postgres every time instead of making it match Python, and Postgres answers CET without complaint, so it would never have caught the four names that drift. The second only changed merge_protections/__init__.py, so the next query that reads a timezone with AT TIME ZONE would hit the same mismatch.

A third option was to ask the running Postgres at startup to convert every name Python knows, and keep the ones where both agree. It would make worker startup depend on Postgres, which hurts on redeploy: the whole fleet of workers would connect to Postgres at once. A fixed list enforced by an integration test gives the same list without that dependency.

What shipped

A pydantic field, PostgresTimeZone, that accepts what Python knows minus six names:

_TIME_ZONES_POSTGRES_READS_OTHERWISE = frozenset(
    {"localtime", "Factory", "CET", "EET", "MET", "WET"},
)

def to_postgres_time_zone(value: typing.Any) -> zoneinfo.ZoneInfo:
    name = value.key if isinstance(value, zoneinfo.ZoneInfo) else value
    if (
        not isinstance(name, str)
        or name in _TIME_ZONES_POSTGRES_READS_OTHERWISE
        or name not in _available_timezones()
    ):
        raise ValueError("Invalid timezone: use an IANA timezone name, such as `Europe/Paris` ...")
    return zoneinfo.ZoneInfo(name)

The isinstance check comes first because the value arrives in a JSON body: review found that sending ["UTC"] as a timezone raised a TypeError on the set lookup: a 500 instead of a 422. The error message tells a CET user to pick a city in their zone. We want freezes in Zone/City form, and no one was using the CET style, so refusing those names is enough for now. Mapping them to a city could come later. The field also got a row in the engine’s AGENTS.md, in the table of types a FastAPI input must declare before it reaches Postgres, so the next endpoint that takes a timezone finds it.

A parity test checks that the accepted names mean the same instant on both sides. For every accepted name that the test Postgres also knows, it converts a January noon and a July noon with AT TIME ZONE in Postgres and ZoneInfo in Python and asserts the instants are equal:

SELECT name, local_time, local_time AT TIME ZONE name
FROM unnest(CAST(:names AS text[])) AS name,
     unnest(CAST(:local_times AS timestamp[])) AS local_time
WHERE name IN (SELECT name FROM pg_timezone_names)
   OR upper(name) IN (SELECT upper(abbrev) FROM pg_timezone_abbrevs)

That WHERE clause is also the test’s blind spot. Our test Postgres knows 487 names and lacks the legacy aliases production carries (Asia/Calcutta, US/Eastern), so the test can only compare the names it has. It asserts that more than 400 names made it through, so a filter that dropped everything would fail instead of passing on nothing.

Before merging, I compared the production image’s Python against production Postgres directly: the only names Postgres lacks are localtime and Factory, and the only ones it reads differently are the four abbreviations. Then I ran the new validator in a copy of the production image against every timezone stored in production: 19 distinct names across 150 freezes, from UTC (104 of them) to Pacific/Kiritimati (one). All 19 pass, including Asia/Calcutta, which is in there three times. Chrome still reports Asia/Calcutta for India, and our dashboard’s freeze form defaults to the browser’s timezone, so legacy aliases have to stay accepted.

Why we kept tzdata-legacy

Removing tzdata-legacy from the engine image looked like the next step, since without it the CET mismatch would disappear (those four names have been legacy aliases since tzdata 2024b), though localtime and Factory would stay. I was three commits in (a table mapping the legacy names to current ones, then the Dockerfile change) before I stopped. The -legacy package is a packaging split, not a sign the names are going away: since trixie, Debian has shipped the older spellings in a separate package, and its own NEWS file says to install tzdata-legacy if “the system provides timezone-aware data over the network (e. g. SQL databases)”. So we rolled it back and kept the package.

What the test doesn’t cover

The fix merged on September 28. I checked agreement with production Postgres once, by hand, including the aliases the test image lacks. In CI, the parity test compares Python against the test Postgres on every run. A Cloud SQL tz update that changes what a name means wouldn’t fail it. Configuration conditions like schedule=…[US/Central] never touch Postgres and still accept every name Python knows.

Storing freezes as UTC timestamptz instead of naive local times would take AT TIME ZONE out of the queries entirely. We may get there, but we wanted the mismatch fixed first.