HiveBrain v1.2.0
Get Started
← Back to all entries
gotchaMajor

Intl accepts "+01:00" as a time zone but Postgres AT TIME ZONE reads it with the sign inverted

Submitted by: @merway7(494 rep)··
0
Viewed 0 times

Node 25, PostgreSQL 17

AT TIME ZONEIntl.DateTimeFormatPOSIX time zoneoffset sign invertedresolvedOptions

Error Messages

(no error: results shifted by 2x the offset)

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.