Skip to main content

PG.L6.TIMEZONE_NOT_UTC — a server whose day boundary is somewhere else

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

This one is not about losing data. A timestamptz is stored as an absolute instant regardless of TimeZone, so the value in the row is right whatever the server is set to.

What moves is every boundary. A cast to date, a date_trunc('day', …), a "today" filter — each of those converts to the current TimeZone first, so on a server set to Europe/Vienna they land on a different day than they would on UTC for the hours between local midnight and UTC midnight. The size of that window changes twice a year with daylight saving, which is why the resulting bug reports arrive in clusters in late March and late October.

The finding is about the server, never about your connection​

Worth stating because it is the most likely misreading: TimeZone is settable per connection — PGTZ, a per-role ALTER ROLE … SET, a client library that sets it on connect. The check reads what the server hands a new connection, not what the audit's own session happens to be running with.

So a correctly configured server audited from a connection running local time is not reported, and a misconfigured one is still reported when the audit's own connection happens to be UTC. Neither direction leans on a coincidence.

Flagged​

ALTER SYSTEM SET "TimeZone" = 'Europe/Vienna';
SELECT pg_reload_conf();

Preferred​

ALTER SYSTEM SET "TimeZone" = 'UTC';
SELECT pg_reload_conf();

The example is server configuration rather than a migration, because that is where the value lives. No migration can set it and no migration can be blamed for it.

Etc/UTC yes, GMT no​

Etc/UTC is accepted alongside UTC. GMT, Zulu and Universal are not, even though they resolve to the same offset — they are the same instant and a different statement, and a server set to one of them was configured by somebody who did not mean UTC. The next person to touch it may well "correct" it to a local zone.

Why the downtime class is none​

ALTER SYSTEM writes the value to a file the server reads, and a parameter of this class takes effect on a configuration reload rather than a restart. There is nothing to schedule and nothing to wait for, which is a rarer answer than it looks among these findings.

Sources​