pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feed8.5.1. Date/Time Input
7+ messages / 3 participants
[nested] [flat]
* 8.5.1. Date/Time Input
@ 2026-09-09 18:23 PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 7+ messages in thread
From: PG Doc comments form @ 2026-09-09 18:23 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: kacperkuras@hotmail.com
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."
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.5.1. Date/Time Input
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
@ 2026-09-17 19:55 ` Bruce Momjian <bruce@momjian.us>
2026-09-18 14:50 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2026-09-17 19:55 UTC (permalink / raw)
To: kacperkuras@hotmail.com; pgsql-docs@lists.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.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.5.1. Date/Time Input
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
@ 2026-09-18 14:50 ` Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 15:48 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
2026-09-18 16:09 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Kacper Kuras @ 2026-09-18 14:50 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.5.1. Date/Time Input
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
2026-09-18 14:50 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
@ 2026-09-18 15:48 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2026-09-18 15:48 UTC (permalink / raw)
To: Kacper Kuras <kacperkuras@hotmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.5.1. Date/Time Input
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
2026-09-18 14:50 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
@ 2026-09-18 16:09 ` Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 16:22 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 7+ messages in thread
From: Kacper Kuras @ 2026-09-18 16:09 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.5.1. Date/Time Input
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
2026-09-18 14:50 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 16:09 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
@ 2026-09-18 16:22 ` Bruce Momjian <bruce@momjian.us>
2026-09-18 16:34 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2026-09-18 16:22 UTC (permalink / raw)
To: Kacper Kuras <kacperkuras@hotmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
Attachments:
[text/x-diff] ISO8601.diff (733B, ../../aq1lYd1pbqjniS6P@momjian.us/2-ISO8601.diff)
download | inline diff:
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 89985ab7b16..dd7af9fda7a 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1879,6 +1879,8 @@ MINUTE TO SECOND
Date and time input is accepted in almost any reasonable format, including
ISO 8601, <acronym>SQL</acronym>-compatible,
traditional <productname>POSTGRES</productname>, and others.
+ (<productname>PostgreSQL</productname> does not support ISO
+ 8601-optional signed years.)
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.5.1. Date/Time Input
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
2026-09-18 14:50 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 16:09 ` Re: 8.5.1. Date/Time Input Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 16:22 ` Re: 8.5.1. Date/Time Input Bruce Momjian <bruce@momjian.us>
@ 2026-09-18 16:34 ` Kacper Kuras <kacperkuras@hotmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kacper Kuras @ 2026-09-18 16:34 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Looks good to me, thanks.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-18 16:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-09 18:23 8.5.1. Date/Time Input PG Doc comments form <noreply@postgresql.org>
2026-09-17 19:55 ` Bruce Momjian <bruce@momjian.us>
2026-09-18 14:50 ` Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 15:48 ` Bruce Momjian <bruce@momjian.us>
2026-09-18 16:09 ` Kacper Kuras <kacperkuras@hotmail.com>
2026-09-18 16:22 ` Bruce Momjian <bruce@momjian.us>
2026-09-18 16:34 ` Kacper Kuras <kacperkuras@hotmail.com>
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