On 2023-11-15 15:46 +0100, Tom Lane wrote:
> Erik Wienhold <e...@ewie.name> writes:
> > On 2023-11-15 12:53 +0100, Peter Eisentraut wrote:
> >> I think we should reframe "ISO" to mean "ISO 9075" and remove all claims of
> >> alignment with ISO 8601 and RFC 3339.
>
> > Agree. So just list the example inputs without any reference to a
> > particular standard, except for ISO 9075 to show that Postgres is
> > SQL-standard-compliant?
>
> I think that would remove useful context without actually improving
> anything. (The datetime input code would be far simpler if it
> meant only to read the exact format mentioned in the SQL spec.)
I wrote the attached patch to hopefully clarify the ISO 8601 references.
The two main changes are:
* Making explicit references to ISO 8601:2004 where section numbers are
referenced. Mostly in source comments but also a couple of places in
the docs. This is about avoiding confusion as ISO 8601:2019 has been
published since then, with different section numbers[1]. The pre-2004
editions also have different section numbers. References to general
ISO 8601 concepts (e.g. week numbers) are left unchanged because those
are not tied to any particular edition.
* Remove the claim that the SQL standard requires ISO 8601 formats as
clarified by Peter Eisentraut. I left the general references to ISO
8601 and RFC 3339 because those relate to the date format that
Postgres implements in addition to the standard SQL formats. Also
change time zone input samples that are described as ISO 8601 but do
not match the standard format.
[1] https://www.iso.org/obp/ui/en/#iso:std:iso:8601:-1:ed-1:v1:en
--
Erik
>From c98e2a86ee4ddaff0e9dc7d2e1e811736c00b48d Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@ewie.name>
Date: Sun, 19 Nov 2023 19:18:07 +0100
Subject: [PATCH v1] Fix references to ISO 8601
* Name the exact edition in sources and docs that reference particular
sections of the standard. ISO 8601:2019 edition was published since
then. So we should spell it out that we reference ISO 8601:2004 to
avoid confusion because other editions have different section numbers
and titles.
* Fix a typo in references to sections 4.4.4.2.1 and 4.4.4.2.2 in
ISO 8601:2004.
* Fix a reference to the ISO 8601:1988 where the edition is not relevant
because it does not name a particular section but only the general
concept of week numbers.
* Fix general references to ISO 8601 in the docs of the datetime data
types. Remove claims that the SQL standard requires ISO 8601 formats.
SQL only references ISO 8601 to define "date" and UTC, but defines its
own date and time syntax. Postgres implements ISO 8601 formats in
addition to the standard.
* Fix time zone input examples that are described as ISO 8601 but do not
match the standard syntax by omitting the leading zero of the hour
component.
---
doc/src/sgml/config.sgml | 2 +-
doc/src/sgml/datatype.sgml | 26 ++++++++++------------
src/backend/utils/adt/datetime.c | 12 +++++-----
src/interfaces/ecpg/pgtypeslib/interval.c | 10 ++++-----
src/interfaces/ecpg/pgtypeslib/timestamp.c | 2 +-
src/test/regress/expected/interval.out | 4 ++--
src/test/regress/sql/interval.sql | 4 ++--
7 files changed, 29 insertions(+), 31 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index fc35a46e5e..a1a3981dd9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9526,7 +9526,7 @@ SET XML OPTION { DOCUMENT | CONTENT };
parameter was set to non-<literal>ISO</literal> output.
The value <literal>iso_8601</literal> will produce output matching the
time
interval <quote>format with designators</quote> defined in section
- 4.4.3.2 of ISO 8601.
+ 4.4.3.2 of ISO 8601:2004.
</para>
<para>
The <varname>IntervalStyle</varname> parameter also affects the
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index e4a7b07033..1c54305e25 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2088,7 +2088,7 @@ MINUTE TO SECOND
<entry>same as 16:05; input hour must be <= 12</entry>
</row>
<row>
- <entry><literal>04:05:06.789-8</literal></entry>
+ <entry><literal>04:05:06.789-08</literal></entry>
<entry>ISO 8601, with time zone as UTC offset</entry>
</row>
<row>
@@ -2150,16 +2150,16 @@ MINUTE TO SECOND
<entry>UTC offset for PST</entry>
</row>
<row>
- <entry><literal>-8:00</literal></entry>
+ <entry><literal>-08:00</literal></entry>
<entry>UTC offset for PST (ISO 8601 extended format)</entry>
</row>
<row>
- <entry><literal>-800</literal></entry>
+ <entry><literal>-0800</literal></entry>
<entry>UTC offset for PST (ISO 8601 basic format)</entry>
</row>
<row>
- <entry><literal>-8</literal></entry>
- <entry>UTC offset for PST (ISO 8601 basic format)</entry>
+ <entry><literal>-08</literal></entry>
+ <entry>UTC offset for PST (ISO 8601)</entry>
</row>
<row>
<entry><literal>zulu</literal></entry>
@@ -2207,7 +2207,7 @@ MINUTE TO SECOND
</programlisting>
and:
<programlisting>
-1999-01-08 04:05:06 -8:00
+1999-01-08 04:05:06 -08:00
</programlisting>
are valid values, which follow the <acronym>ISO</acronym> 8601
@@ -2416,15 +2416,13 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
(Unix <application>date</application> format), or
German. The default
is the <acronym>ISO</acronym> format. (The
- <acronym>SQL</acronym> standard requires the use of the ISO 8601
- format. The name of the <quote>SQL</quote> output format is a
+ <acronym>SQL</acronym> standard defines <quote>date</quote> in terms of
ISO 8601.
+ The name of the <quote>SQL</quote> output format is a
historical accident.) <xref
linkend="datatype-datetime-output-table"/> shows examples of each
output style. The output of the <type>date</type> and
<type>time</type> types is generally only the date or time part
- in accordance with the given examples. However, the
- <productname>POSTGRES</productname> style outputs date-only values in
- <acronym>ISO</acronym> format.
+ in accordance with the given examples.
</para>
<table id="datatype-datetime-output-table">
@@ -2771,8 +2769,8 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
<para>
Interval values can also be written as ISO 8601 time intervals, using
- either the <quote>format with designators</quote> of the standard's
section
- 4.4.3.2 or the <quote>alternative format</quote> of section 4.4.3.3. The
+ either the <quote>format with designators</quote> or the
<quote>alternative
+ format</quote> (sections 4.4.3.2 and 4.4.3.3 in the 2004 edition). The
format with designators looks like this:
<synopsis>
P <replaceable>quantity</replaceable> <replaceable>unit</replaceable>
<optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable>
...</optional> <optional> T <optional> <replaceable>quantity</replaceable>
<replaceable>unit</replaceable> ...</optional></optional>
@@ -2995,7 +2993,7 @@ SELECT EXTRACT(days from '80 hours'::interval);
<para>
The output of the <literal>iso_8601</literal> style matches the
<quote>format
with designators</quote> described in section 4.4.3.2 of the
- ISO 8601 standard.
+ ISO 8601:2004 standard.
</para>
<table id="interval-style-output-table">
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index fca9a2a6e9..01f36c3aec 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3724,7 +3724,7 @@ ISO8601IntegerWidth(char *fieldstart)
/* DecodeISO8601Interval()
- * Decode an ISO 8601 time interval of the "format with designators"
+ * Decode an ISO 8601:2004 time interval of the "format with designators"
* (section 4.4.3.2) or "alternative format" (section 4.4.3.3)
* Examples: P1D for 1 day
* PT1H for 1 hour
@@ -3805,7 +3805,7 @@ DecodeISO8601Interval(char *str,
!AdjustFractMicroseconds(fval,
USECS_PER_DAY, itm_in))
return DTERR_FIELD_OVERFLOW;
break;
- case 'T': /* ISO 8601 4.4.3.3
Alternative Format / Basic */
+ case 'T': /* ISO 8601:2004
4.4.3.3 Alternative Format / Basic */
case '\0':
if (ISO8601IntegerWidth(fieldstart) ==
8 && !havefield)
{
@@ -3822,7 +3822,7 @@ DecodeISO8601Interval(char *str,
}
/* Else fall through to extended
alternative format */
/* FALLTHROUGH */
- case '-': /* ISO 8601 4.4.3.3
Alternative Format,
+ case '-': /* ISO 8601:2004
4.4.3.3 Alternative Format,
* Extended */
if (havefield)
return DTERR_BAD_FORMAT;
@@ -3893,7 +3893,7 @@ DecodeISO8601Interval(char *str,
if (!AdjustMicroseconds(val, fval,
USECS_PER_SEC, itm_in))
return DTERR_FIELD_OVERFLOW;
break;
- case '\0': /* ISO 8601 4.4.3.3
Alternative Format */
+ case '\0': /* ISO 8601:2004
4.4.3.3 Alternative Format */
if (ISO8601IntegerWidth(fieldstart) ==
6 && !havefield)
{
if (!AdjustMicroseconds(val /
10000, 0, USECS_PER_HOUR, itm_in) ||
@@ -3905,7 +3905,7 @@ DecodeISO8601Interval(char *str,
}
/* Else fall through to extended
alternative format */
/* FALLTHROUGH */
- case ':': /* ISO 8601 4.4.3.3
Alternative Format,
+ case ':': /* ISO 8601:2004
4.4.3.3 Alternative Format,
* Extended */
if (havefield)
return DTERR_BAD_FORMAT;
@@ -4596,7 +4596,7 @@ EncodeInterval(struct pg_itm *itm, int style, char *str)
}
break;
- /* ISO 8601 "time-intervals by duration only" */
+ /* ISO 8601:2004 4.4.4.2 "time intervals by duration" */
case INTSTYLE_ISO_8601:
/* special-case zero to avoid printing nothing */
if (year == 0 && mon == 0 && mday == 0 &&
diff --git a/src/interfaces/ecpg/pgtypeslib/interval.c
b/src/interfaces/ecpg/pgtypeslib/interval.c
index 936a688381..fa23eb2fab 100644
--- a/src/interfaces/ecpg/pgtypeslib/interval.c
+++ b/src/interfaces/ecpg/pgtypeslib/interval.c
@@ -169,7 +169,7 @@ DecodeISO8601Interval(char *str,
tm->tm_mday += val;
AdjustFractSeconds(fval, tm, fsec,
SECS_PER_DAY);
break;
- case 'T': /* ISO 8601 4.4.3.3
Alternative Format / Basic */
+ case 'T': /* ISO 8601:2004
4.4.3.3 Alternative Format / Basic */
case '\0':
if (ISO8601IntegerWidth(fieldstart) ==
8 && !havefield)
{
@@ -185,7 +185,7 @@ DecodeISO8601Interval(char *str,
}
/* Else fall through to extended
alternative format */
/* FALLTHROUGH */
- case '-': /* ISO 8601 4.4.3.3
Alternative Format,
+ case '-': /* ISO 8601:2004
4.4.3.3 Alternative Format,
* Extended */
if (havefield)
return DTERR_BAD_FORMAT;
@@ -253,7 +253,7 @@ DecodeISO8601Interval(char *str,
tm->tm_sec += val;
AdjustFractSeconds(fval, tm, fsec, 1);
break;
- case '\0': /* ISO 8601 4.4.3.3
Alternative Format */
+ case '\0': /* ISO 8601:2004
4.4.3.3 Alternative Format */
if (ISO8601IntegerWidth(fieldstart) ==
6 && !havefield)
{
tm->tm_hour += val / 10000;
@@ -264,7 +264,7 @@ DecodeISO8601Interval(char *str,
}
/* Else fall through to extended
alternative format */
/* FALLTHROUGH */
- case ':': /* ISO 8601 4.4.3.3
Alternative Format,
+ case ':': /* ISO 8601:2004
4.4.3.3 Alternative Format,
* Extended */
if (havefield)
return DTERR_BAD_FORMAT;
@@ -850,7 +850,7 @@ EncodeInterval(struct /* pg_ */ tm *tm, fsec_t fsec, int
style, char *str)
}
break;
- /* ISO 8601 "time-intervals by duration only" */
+ /* ISO 8601:2004 4.4.4.2 "time intervals by duration" */
case INTSTYLE_ISO_8601:
/* special-case zero to avoid printing nothing */
if (year == 0 && mon == 0 && mday == 0 &&
diff --git a/src/interfaces/ecpg/pgtypeslib/timestamp.c
b/src/interfaces/ecpg/pgtypeslib/timestamp.c
index f1b143fbd2..2fa3f85d01 100644
--- a/src/interfaces/ecpg/pgtypeslib/timestamp.c
+++ b/src/interfaces/ecpg/pgtypeslib/timestamp.c
@@ -596,7 +596,7 @@ dttofmtasc_replace(timestamp * ts, date dDate, int dow,
struct tm *tm,
break;
/*
- * The ISO 8601:1988 week number of the
current year as a
+ * The ISO 8601 week number of the
current year as a
* decimal number.
*/
case 'V':
diff --git a/src/test/regress/expected/interval.out
b/src/test/regress/expected/interval.out
index b79b6fcd4d..315efc93f1 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -1022,7 +1022,7 @@ select interval '0' AS
"zero",
PT0S | P1Y2M | P1DT2H3M4S | PT2H3M4.45679S |
P1Y2M3DT4H5M6.7S | P1Y2M-3DT-4H-5M-6.7S | P-1Y-2M3DT4H5M6.7S
(1 row)
--- test inputting ISO 8601 4.4.2.1 "Format With Time Unit Designators"
+-- test inputting ISO 8601:2004 4.4.4.2.1 "Format with designators"
SET IntervalStyle to sql_standard;
select interval 'P0Y' AS "zero",
interval 'P1Y2M' AS "a year 2 months",
@@ -1036,7 +1036,7 @@ select interval 'P0Y' AS "zero",
0 | 1-2 | 7 0:00:00 | 1 2:03:04 | +1-2 +3 +4:05:06.7 |
-1-2 -3 -4:05:06.7 | -0:00:00.1
(1 row)
--- test inputting ISO 8601 4.4.2.2 "Alternative Format"
+-- test inputting ISO 8601:2004 4.4.4.2.2 "Alternative format"
SET IntervalStyle to postgres;
select interval 'P00021015T103020' AS "ISO8601 Basic Format",
interval 'P0002-10-15T10:30:20' AS "ISO8601 Extended Format";
diff --git a/src/test/regress/sql/interval.sql
b/src/test/regress/sql/interval.sql
index 5566ad0e51..f4b1ffc424 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -323,7 +323,7 @@ select interval '0' AS
"zero",
(interval '1-2' - interval '3 4:05:06.7') AS "mixed sign",
(- interval '1-2' + interval '3 4:05:06.7') AS "negative";
--- test inputting ISO 8601 4.4.2.1 "Format With Time Unit Designators"
+-- test inputting ISO 8601:2004 4.4.4.2.1 "Format with designators"
SET IntervalStyle to sql_standard;
select interval 'P0Y' AS "zero",
interval 'P1Y2M' AS "a year 2 months",
@@ -333,7 +333,7 @@ select interval 'P0Y' AS "zero",
interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
interval 'PT-0.1S' AS "fractional second";
--- test inputting ISO 8601 4.4.2.2 "Alternative Format"
+-- test inputting ISO 8601:2004 4.4.4.2.2 "Alternative format"
SET IntervalStyle to postgres;
select interval 'P00021015T103020' AS "ISO8601 Basic Format",
interval 'P0002-10-15T10:30:20' AS "ISO8601 Extended Format";
--
2.42.1
