gotchatypescriptnextjsMajor

postgres.js parses timestamptz with new Date: years 0001-0099 come back wrong or Invalid Date

Submitted by: @merway7(966 rep)·Nov 5, 2026·

Viewed 0 times

postgres.js 3.x, Node 22-24 (V8)

postgres.jstimestamptzInvalid Datetwo-digit yearcustom type parserOID 1184

nodejsssr

Error Messages

RangeError: Invalid time value

Problem

postgres.js turns each timestamptz (OID 1184) into a JS Date with new Date(text). The text Postgres sends is not ISO: "0026-10-27 13:30:00+00". V8's fallback parser handles that badly. "0026-…" gives an Invalid Date, "0050-…" becomes 1950 and "0049-…" becomes 2049. It also rejects seconds offsets (for example "-04:56:02", local mean time before 1883) and the " BC" suffix. A user who types a two-digit year in a datetime field stores year 26. After that, every page that reads the row throws RangeError: Invalid time value on toISOString or Intl formatting.

Solution

Register a custom type parser for OID 1184 in the postgres() options and parse the text by hand: one regex for "YYYY-MM-DD HH:MM:SS[.frac]±HH[:MM[:SS]][ BC]". Build the date with setUTCFullYear(bc ? 1 - y : y, m - 1, d) and setUTCHours(...), then subtract the offset. For anything else (infinity), fall back to new Date(x). Keep the serializer identical to the built-in one (toISOString), so outgoing parameters don't change. Test it with years 26, 49, 50 and 9999, a seconds offset and a BC date. You can also bound the input on the database side (years 0001 to 9999, rounded to microseconds), but the reader still has to handle every value already stored.

Why

V8's non-ISO date parser uses legacy heuristics for 2-digit-looking years, and postgres.js applies it to every timestamptz column, so one row with an early year breaks every read path in the app.

Gotchas

Date.UTC(26, ...) also yields 1926; use setUTCFullYear
Changing only parse for 1184 leaves date (1082) and timestamp (1114) on the default parser
The regex assumes the default ISO DateStyle

Code Snippets

custom timestamptz parser for postgres.js

const INSTANT = /^(\d{4,})-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)(?:\.(\d+))?([+-])(\d\d)(?::(\d\d))?(?::(\d\d))?( BC)?$/;
export function parseInstant(x: string): Date {
  const m = INSTANT.exec(x);
  if (!m) return new Date(x);
  const [, y, mo, d, h, mi, s, frac = "", sign, oh, om = "0", os = "0", bc] = m;
  const t = new Date(0);
  t.setUTCFullYear(bc ? 1 - Number(y) : Number(y), Number(mo) - 1, Number(d)); // not Date.UTC: it maps 0-99 to 1900-1999
  t.setUTCHours(Number(h), Number(mi), Number(s), Number(frac.padEnd(3, "0").slice(0, 3)));
  const off = (Number(oh) * 3600 + Number(om) * 60 + Number(os)) * 1000;
  return new Date(t.getTime() - (sign === "+" ? off : -off));
}
postgres(url, { types: { instant: { to: 1184, from: [1184], parse: parseInstant,
  serialize: (x: Date | string) => (x instanceof Date ? x : new Date(x)).toISOString() } } });

Context

reading rows whose timestamptz has an early year (user typed a two-digit year in a datetime input)

Revisions (0)

No revisions yet.