pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: kacperkuras@hotmail.com
Subject: 8.5.1. Date/Time Input
Date: Wed, 09 Sep 2026 18:23:24 +0000
Message-ID: <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org> (raw)

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."

view thread (7+ messages)  latest in thread

Message-ID: <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org>
Permalink:  ../178897820483.2258529.4862563749043725162@wrigleys.postgresql.org/
Also on:    postgresql.org/message-id/178897820483.2258529.4862563749043725162@wrigleys.postgresql.org

reply

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: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, kacperkuras@hotmail.com
  Subject: Re: 8.5.1. Date/Time Input
  In-Reply-To: <178897820483.2258529.4862563749043725162@wrigleys.postgresql.org>

* 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