8.5.1. Date/Time Input
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253753psql -h localhost -U postgresBuilt from patchset v6 (message #6), September 20, 2026 at 04:37 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253753_6 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253753_6 && git checkout t253753_6Patchset v6 (message #6) is on t253753_6
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/datatype-datetime.html
Description:
Section 8.5.1 says date/time input is accepted "in almost any reasonable
format, including ISO 8601".
That holds for years 0001..9999 but not outside them, and I could not find
the limit stated anywhere.
ISO 8601 writes years outside that range with an explicit sign and more than
four digits. PostgreSQL
rejects those, while holding and printing the very same values in its own
spelling:
SELECT '10000-01-02'::date; -- 10000-01-02
SELECT '+10000-01-02'::date; -- ERROR: time zone displacement out of
range: "+10000-01-02"
SELECT '-0001-01-02'::date; -- ERROR: invalid input syntax for type
date: "-0001-01-02"
SELECT '0000-01-02'::date; -- ERROR: date/time field value out of
range: "0000-01-02"
Per B.1, a token starting with + or - is read as a numeric time zone, and
the first error names that directly. The negative forms fail differently -
as plain syntax rather than as a displacement - so I have not assumed the
same cause for them. Either way, ISO 8601 also counts through a year zero
where PostgreSQL counts BC from one, so ISO -0001 (2 BC) has no ISO spelling
PostgreSQL accepts.
I ran into this writing a PostgreSQL driver for Kotlin: for such a year, the
ISO 8601 that Kotlin's date library produces is a string PostgreSQL will not
read back — for a date it stores and prints happily.
Suggested wording — qualify the claim rather than describe the parser, e.g.:
"...including ISO 8601 (for years 0001-9999; ISO 8601 expanded years
carry an explicit sign, which
is read as a time zone offset — write 10000-01-02 or 0002-01-02 BC
instead), SQL-compatible,
traditional POSTGRES, and others."
On Wed, Sep 9, 2026 at 06:23:24PM +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/datatype-datetime.html
Description:Section 8.5.1 says date/time input is accepted "in almost any reasonable
format, including ISO 8601".
That holds for years 0001..9999 but not outside them, and I could not find
the limit stated anywhere.ISO 8601 writes years outside that range with an explicit sign and more than
four digits. PostgreSQL
rejects those, while holding and printing the very same values in its own
spelling:SELECT '10000-01-02'::date; -- 10000-01-02
SELECT '+10000-01-02'::date; -- ERROR: time zone displacement out of
range: "+10000-01-02"
SELECT '-0001-01-02'::date; -- ERROR: invalid input syntax for type
date: "-0001-01-02"
SELECT '0000-01-02'::date; -- ERROR: date/time field value out of
range: "0000-01-02"Per B.1, a token starting with + or - is read as a numeric time zone, and
the first error names that directly. The negative forms fail differently -
as plain syntax rather than as a displacement - so I have not assumed the
same cause for them. Either way, ISO 8601 also counts through a year zero
where PostgreSQL counts BC from one, so ISO -0001 (2 BC) has no ISO spelling
PostgreSQL accepts.I ran into this writing a PostgreSQL driver for Kotlin: for such a year, the
ISO 8601 that Kotlin's date library produces is a string PostgreSQL will not
read back — for a date it stores and prints happily.Suggested wording — qualify the claim rather than describe the parser, e.g.:
"...including ISO 8601 (for years 0001-9999; ISO 8601 expanded years
carry an explicit sign, which
is read as a time zone offset — write 10000-01-02 or 0002-01-02 BC
instead), SQL-compatible,
traditional POSTGRES, and others."
Good point; for reference:
https://en.wikipedia.org/wiki/ISO_8601#Dates
I have written the attached patch.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
Thanks. In the patch, "without a sign" only holds for years after
9999: dropping the minus from -0001-01-02 gives 0001-01-02, which is
1 AD, not 2 BC. Perhaps:
(ISO 8601 writes years after 9999 with a leading plus sign, and
years before 1 AD from a year zero, so 0000 is 1 BC and -0001 is
2 BC. PostgreSQL accepts neither; write 10000-01-02 and
0002-01-02 BC instead.)
On Fri, Sep 18, 2026 at 02:50:20PM +0000, Kacper Kuras wrote:
Thanks. In the patch, "without a sign" only holds for years after
9999: dropping the minus from -0001-01-02 gives 0001-01-02, which is
1 AD, not 2 BC. Perhaps:(ISO 8601 writes years after 9999 with a leading plus sign, and
years before 1 AD from a year zero, so 0000 is 1 BC and -0001 is
2 BC. PostgreSQL accepts neither; write 10000-01-02 and
0002-01-02 BC instead.)
Looking at the wiki page again, I see:
To represent years before 0000 or after 9999, the standard also
permits the expansion of the year representation but only by prior
agreement between the sender and the receiver.[25] An expanded
year representation [±YYYYY] must have an agreed-upon number of
extra year digits beyond the four-digit minimum, and it must be
prefixed with a + or - sign[26] instead of the more common AD/BC
(or CE/BCE) notation; by convention 1 BC is labelled +0000,
2 BC is labeled -0001, and so on.[27]
The "agreement between the sender and the receiver" makes it seem we
don't need to document that we don't support signs on the years.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
The "agreement between the sender and the receiver" makes it seem we
don't need to document that we don't support signs on the years.
Agreement needs each side to state its terms, though, and PostgreSQL
states its terms in the documentation. It already does for the other
by-agreement range: ISO 8601 leaves years before 1583 to agreement,
and B.6 settles them - proleptic Gregorian for all dates - if you
think to look in an appendix on calendar history. For a leading sign
there is only B.1's tokenizing rule, "either a numeric time zone or
a special field", which says nothing about years. So a client finds
out by trying: -0001-01-02, the wiki's own 2 BC, is refused as
invalid input syntax, and +10000-01-02 as a time zone displacement.
On Fri, Sep 18, 2026 at 04:09:39PM +0000, Kacper Kuras wrote:
The "agreement between the sender and the receiver" makes it seem we
don't need to document that we don't support signs on the years.Agreement needs each side to state its terms, though, and PostgreSQL
states its terms in the documentation. It already does for the other
by-agreement range: ISO 8601 leaves years before 1583 to agreement,
and B.6 settles them - proleptic Gregorian for all dates - if you
think to look in an appendix on calendar history. For a leading sign
there is only B.1's tokenizing rule, "either a numeric time zone or
a special field", which says nothing about years. So a client finds
out by trying: -0001-01-02, the wiki's own 2 BC, is refused as
invalid input syntax, and +10000-01-02 as a time zone displacement.
How about this patch?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.