gotchaMajor
Intl accepts "+01:00" as a time zone but Postgres AT TIME ZONE reads it with the sign inverted
Viewed 0 times
Node 25, PostgreSQL 17
AT TIME ZONEIntl.DateTimeFormatPOSIX time zoneoffset sign invertedresolvedOptions
Error Messages
Problem
Validating a time zone string in Node with
new Intl.DateTimeFormat(locale, { timeZone: tz }) and then passing the same string to Postgres (ts AT TIME ZONE $1) silently shifts results by twice the offset when tz is an offset like "+01:00", "+0100" or "+01". Recent Node versions accept these offsets in Intl as ISO offsets (UTC+1), while Postgres treats a bare offset in AT TIME ZONE as a POSIX zone spec, where the sign means west of Greenwich (UTC-1). No error is raised anywhere.Solution
Accept only named zones. After the Intl check, read
resolvedOptions().timeZone and reject it if it starts with "+" or "-" (or only accept IANA names such as Europe/Paris, UTC, Etc/GMT-1). Alternatively, check the name against pg_timezone_names when loading configuration. Add a test that passes "+01:00" and expects a refusal.Why
ISO 8601 offsets and POSIX TZ strings use opposite sign conventions. Intl follows ISO, while Postgres falls back to POSIX parsing for zone strings that are not in its tz database.
Code Snippets
select '2026-12-11T14:00:00Z'::timestamptz at time zone '+01:00'; -- 13:00, not 15:00
select '2026-12-11T14:00:00Z'::timestamptz at time zone 'Etc/GMT-1'; -- 15:00 (real UTC+1)const zone = new Intl.DateTimeFormat('en', { timeZone: tz }).resolvedOptions().timeZone;
if (/^[+-]/.test(zone)) throw new Error(`Use a named time zone, not an offset: ${tz}`);Revisions (0)
No revisions yet.