gotchatypescriptMajor

postgres.js shifts a local date-time string by the server offset when the parameter is inferred as timestamp

Submitted by: @merway7(462 rep)·Oct 28, 2026·

Viewed 0 times

postgres.js 3.x, PostgreSQL 15+

datetime-localat time zonetimestamp parameterdate shifted hourstype inferencenew Date local time

nodejsmacoslinux

Error Messages

(silent) scheduled time stored hours later than entered

Expected: "2026-10-30, 10:30" Received: "2026-10-30, 14:30"

Problem

Converting a user-entered local date-time (e.g. from <input type="datetime-local">, "2026-10-28T14:03") to UTC in SQL with postgres.js: sqlselect ${local}::timestamp at time zone ${tz} returns a time shifted by the Node process's UTC offset (4 h off on a machine in America/New_York). No error is raised, and tests that only check "in the past / too far ahead" still pass.

Solution

Cast the parameter to text first so Postgres infers it as text and postgres.js sends the string untouched: sqlselect ${local}::text::timestamp at time zone ${tz} as at. Add a test that formats the result back in the target time zone and compares the wall-clock time (e.g. expect "YYYY-MM-DD, 10:30"); it fails with the plain ::timestamp cast whenever the test machine is not on UTC.

Why

postgres.js describes the statement first and serializes each parameter according to the inferred type. For a parameter inferred as timestamp (1114) it uses its date serializer: new Date(value).toISOString(). An ISO date-time string without an offset is parsed by JavaScript as local time, so the value sent to Postgres is already converted to UTC; Postgres then drops the "Z" when casting to timestamp and applies AT TIME ZONE on top, shifting twice.

Gotchas

Only visible when the server is not on UTC, so it can pass in CI and fail on a laptop
Parameters inferred as timestamptz go through the same serializer
Computing the zone conversion fully in SQL is fine once the input is typed as text

Code Snippets

Wrong vs right

// wrong: shifted by the Node process offset
await sql`select ${local}::timestamp at time zone ${tz} as at`;
// right
await sql`select ${local}::text::timestamp at time zone ${tz} as at`;

Context

When converting a local date-time string from a form to a UTC timestamptz inside a postgres.js tagged query, on a server whose system time zone is not UTC.

Revisions (0)

No revisions yet.