Date: Thu, 17 Sep 2026 15:55:07 -0400
From: Bruce Momjian <bruce@momjian.us>
To: kacperkuras@hotmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: 8.5.1. Date/Time Input
Message-ID: <aqxFm0lsIiHIJk3A@momjian.us>
References: <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="zxDU5M0ykofYuQD7"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/aqxFm0lsIiHIJk3A%40momjian.us>
Precedence: bulk


--zxDU5M0ykofYuQD7
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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.

--zxDU5M0ykofYuQD7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="ISO8601.diff"

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 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

--zxDU5M0ykofYuQD7--