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

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

> On Mon, 3 Jul 2023 at 20:06, Dave Cramer <davecramer(at)gmail(dot)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