Why is DATESTYLE, ordering ignored for output but used for input ?

On Mon, 3 Jul 2023 at 20:06, Dave Cramer <davecramer@gmail.com> wrote:

Greetings,

For ISO and German dates the order DMY is completely ignored on output but used for input.

test=# set datestyle to 'ISO,DMY';
SET
select '7-8-2023'::date
test-# ;
date
------------
2023-08-07
(1 row)

test=# set datestyle to 'ISO,MDY';
SET
test=# select '7-8-2023'::date
;
date
------------
2023-07-08
(1 row)

Note regardless of how the ordering is specified it is always output as
YMD

Wouldn't that be because ISO only has one correct ordering of the day
and month fields? I fail to see why we'd output non-ISO-formatted date
strings when ISO format is requested. I believe the reason is the same
for German dates - Germany's official (or most common?) date
formatting has a single ordering of these fields, which is also the
ordering that we supply.

The code comments also seem to hint to this:

case USE_ISO_DATES:
case USE_XSD_DATES:
/* compatible with ISO date formats */

case USE_GERMAN_DATES:
/* German-style date format */

This has been this way since the code for ISO was originally committed
in July of '97 with 8507ddb9 and the GERMAN formatting which was added
in December of '97 as D.M/Y with 352b3687 (and later that month was
updated to D.M.Y with ca23837a).
Sadly, the -hackers archives don't seem to have any mails from that
time period, so I couldn't find much info on the precise rationale
around this behavior.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

PS. That was some interesting digging into the history of the date
formatting module.

On Mon, 3 Jul 2023 at 17:13, Matthias van de Meent <
boekewurm+postgres@gmail.com> wrote:

On Mon, 3 Jul 2023 at 20:06, Dave Cramer <davecramer@gmail.com> wrote:

Greetings,

For ISO and German dates the order DMY is completely ignored on output

but used for input.

test=# set datestyle to 'ISO,DMY';
SET
select '7-8-2023'::date
test-# ;
date
------------
2023-08-07
(1 row)

test=# set datestyle to 'ISO,MDY';
SET
test=# select '7-8-2023'::date
;
date
------------
2023-07-08
(1 row)

Note regardless of how the ordering is specified it is always output as
YMD

Wouldn't that be because ISO only has one correct ordering of the day
and month fields? I fail to see why we'd output non-ISO-formatted date
strings when ISO format is requested. I believe the reason is the same
for German dates - Germany's official (or most common?) date
formatting has a single ordering of these fields, which is also the
ordering that we supply.

seems rather un-intuitive that it works for some datestyles and not for
others

The code comments also seem to hint to this:

case USE_ISO_DATES:
case USE_XSD_DATES:
/* compatible with ISO date formats */

case USE_GERMAN_DATES:
/* German-style date format */

This has been this way since the code for ISO was originally committed
in July of '97 with 8507ddb9 and the GERMAN formatting which was added
in December of '97 as D.M/Y with 352b3687 (and later that month was
updated to D.M.Y with ca23837a).
Sadly, the -hackers archives don't seem to have any mails from that
time period, so I couldn't find much info on the precise rationale
around this behavior.

Yeah, I couldn't find much either.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech/)

PS. That was some interesting digging into the history of the date
formatting module.

Always interesting digging into the history of the project.

Dave