From: Bruce Momjian <bruce@momjian.us>
To: kacperkuras@hotmail.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: 8.5.1. Date/Time Input
Date: Thu, 17 Sep 2026 15:55:07 -0400
Message-ID: <aqxFm0lsIiHIJk3A@momjian.us> (raw)
In-Reply-To: <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org>
References: <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org>
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.
Attachments:
[text/x-diff] ISO8601.diff (902B, ../aqxFm0lsIiHIJk3A@momjian.us/2-ISO8601.diff)
download | inline diff:diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgmlindex 89985ab7b16..6bc0de06988 100644--- a/doc/src/sgml/datatype.sgml+++ b/doc/src/sgml/datatype.sgml@@ -1878,7 +1878,10 @@ MINUTE TO SECOND
<para>
Date and time input is accepted in almost any reasonable format, including
ISO 8601, <acronym>SQL</acronym>-compatible,
- traditional <productname>POSTGRES</productname>, and others.+ traditional <productname>POSTGRES</productname>, and others. (ISO+ 8601 requires years of more than four digits to be preceded by a plus+ or minus sign; <productname>PostgreSQL</productname> supports such+ years, but without a sign.)
For some formats, ordering of day, month, and year in date input is
ambiguous and there is support for specifying the expected
ordering of these fields. Set the <xref linkend="guc-datestyle"/> parameter
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, kacperkuras@hotmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: 8.5.1. Date/Time Input
In-Reply-To: <aqxFm0lsIiHIJk3A@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox