Repository navigation
fix!: write date and timestamp years outside 0000-9999 as ISO 8601 does - #153
Conversation
A date or timestamp before 1 AD or past 9999 came back as the server's text (`0002-06-01 BC`, `12345-01-01`), which Temporal refuses; it now takes the extended year a timestamptz already had (`-000001-06-01`, `+012345-01-01`), and a parameter reads it back.
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
🟢 Approval recommended
No active review issues remain for the ISO 8601 date and timestamp formatting changes.
No open issues 🎉
What changed in this PR
This PR makes Reylo’s Postgres date and timestamp output use ISO 8601 year formatting, consistent with timestamptz. This is a breaking change for BC years and years past 9999; ordinary AD dates and infinity values remain unchanged.
Changes:
- Normalizes extended years in server text, arrays, and driver-provided Dates.
- Accepts signed years with six or more digits when converting parameters back to Postgres.
- Adds decoding, parameter round-trip, and cross-driver regression coverage, and documents the new representation.
| File | Description |
|---|---|
| tests/values.test.ts | Tests extended years, Temporal compatibility, arrays, and round trips. |
| tests/representation.test.ts | Adds cross-driver cases for BC dates and extended-year timestamps. |
| tests/execute.test.ts | Checks conversion of BC timestamps and seven-digit date parameters. |
| tests/decode.test.ts | Updates Bun.sql Date decoding expectations and adds an extended-year case. |
| src/values.ts | Normalizes output years and expands parameter year parsing. |
| site/content/docs/postgres.mdx | Documents extended-year formatting and Temporal limitations. |
Review effort: Balanced
There was a problem hiding this comment.
ℹ️ No code issues found — one docs gap inline.
Reviewed changes
Reviewed the change that writes Postgres date and timestamp years outside 0000–9999 in ISO 8601's extended form, as timestamptz already did.
isoDatedecoder —dateandtimestamptext (and their arrays, throughtextDecoder) now become-000001-06-01/+012345-01-01; four-digit AD years and±infinitytake the fast path untouched.- Bun.sql Dates —
utcTextwrites the year withisoYearand no longer appendsBC, so Bun's values match the server text path. - Parameter round trip —
postgresYearaccepts[+-]\d{6,}, so a seven-digitdate(+5874897-12-31) goes back in as5874897-12-31. - Docs —
postgres.mdx's "Years past 9999 or before 1 AD" now covers all three types.
I traced every Postgres decode path (pg, pg-promise, postgres.js .raw(), PGlite, Slonik, Standard Query) to textDecoder, and confirmed MySQL, SQLite and DuckDB don't use TEXT_DECODERS. The touched unit tests in values, decode and execute pass locally; the new values test needs a global Temporal, just like the existing timestamptz test.
claude-opus-5-5 ｜ 𝕏
There was a problem hiding this comment.
✅ No new issues found — the docs gap from the last review is closed.
Reviewed changes
Reviewed the one docs commit pushed since the last Pullfrog review.
- Added JSON vs column note — the "Values inside a document" section of
postgres.mdxnow says adateortimestampbefore 1 AD or past 9999 keeps Postgres's own year inside JSON. It includes the pairjsonb_build_object('d', '0002-06-01 BC'::date)→{ d: "0002-06-01 BC" }. That matches how Postgres writes JSON dates:EncodeDateOnlywithUSE_XSD_DATESadds theBCsuffix.
claude-opus-5-5 ｜ 𝕏
A
dateortimestampwhose year is before 1 AD or past 9999 came back as Postgres's own text (0002-06-01 BC,12345-01-01,0002-06-01T12:00:00 BC), whichTemporal.PlainDate.fromandTemporal.PlainDateTime.fromrefuse. They now take the extended year atimestamptzalready had: four digits for 0000–9999, else a sign and six digits, the year 0000 being 1 BC.'0002-06-01 BC'::date→-000001-06-01'12345-01-01'::date→+012345-01-01'0002-06-01 12:00:00 BC'::timestamp→-000001-06-01T12:00:00'10000-01-01 00:00:00'::timestamp→+010000-01-01T00:00:00infinityand-infinitystay those words; a four-digit AD year is untouched (the fast path checks only where the first-falls and a trailingBC).A
datepast 999999 AD (Postgres'sdatereaches 5874897) keeps the sign and takes the digits it needs,+5874897-12-31: ISO 8601 leaves the number of extended digits to agreement,Temporalholds no such date in any form, and a sign keeps every year outside 0000–9999 in one form. The parameter conversion now reads a sign followed by six or more digits, so every value.run()returns for these types goes back in.The same text comes from every driver: the server's text (pg, pg-promise, postgres.js, PGlite, Neon, Slonik, Standard Query), arrays element by element, and Bun.sql's Dates, which previously wrote a five-digit year without a sign.
This changes the text of those values, hence
!.Tests:
tests/values.test.ts(BC, five-, six- and seven-digit years for both types, Temporal reading each, the parameter round trip, infinity,date[]andtimestamp[], Dates),tests/execute.test.ts(the parameters sent),tests/decode.test.ts(Bun.sql),tests/representation.test.ts(a BC date, a five-digit timestamp and atimestamp[]on every live driver). Docs:postgres.mdx's "Years past 9999 or before 1 AD".