Re: Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'

Cleysson Lima <cleyssondba(at)gmail(dot)com> writes:
> this is a review of the patch: chg_to_date_yyyywwd.patch
> There hasn't been any problem, at least that I've been able to find.

AFAICS, the point of this patch is to make to_date symmetrical
with the definition of WW that the other patch wants for to_char.
But the other patch is wrong, for the reasons I explained upthread,
so I doubt that we want this one either.

I still think that it'd be necessary to invent at least one new
format field code in order to get to a sane version of this feature.
As they stand, 'WW' and 'D' do not agree on what a week is, and
changing the behavior of either one in order to make them agree
is just not going to happen.

BTW, I went to check on what Oracle thinks about this, since these
functions are allegedly Oracle-compatible. On PG, I get this
for the WW and D values for the next few days:

select to_char(current_date+n, 'YYYY-MM-DD YYYY-WW-D Day')
from generate_series(0,10) n;
to_char
--------------------------------
2020-01-31 2020-05-6 Friday
2020-02-01 2020-05-7 Saturday
2020-02-02 2020-05-1 Sunday
2020-02-03 2020-05-2 Monday
2020-02-04 2020-05-3 Tuesday
2020-02-05 2020-06-4 Wednesday
2020-02-06 2020-06-5 Thursday
2020-02-07 2020-06-6 Friday
2020-02-08 2020-06-7 Saturday
2020-02-09 2020-06-1 Sunday
2020-02-10 2020-06-2 Monday
(11 rows)

I did the same calculations using Oracle 11g R2 on sqlfiddle.com
and got the same results. Interestingly, though, I also tried it on

https://rextester.com/l/oracle_online_compiler

and here's what I get there:

2020-01-31 2020-05-5 Freitag
2020-02-01 2020-05-6 Samstag
2020-02-02 2020-05-7 Sonntag
2020-02-03 2020-05-1 Montag
2020-02-04 2020-05-2 Dienstag
2020-02-05 2020-06-3 Mittwoch
2020-02-06 2020-06-4 Donnerstag
2020-02-07 2020-06-5 Freitag
2020-02-08 2020-06-6 Samstag
2020-02-09 2020-06-7 Sonntag
2020-02-10 2020-06-1 Montag

(I don't know how to switch locales on these sites, so I don't have
any way to check what happens in other locales.)

So we agree with Oracle on what WW means, but they count D as 1-7
starting on either Sunday or Monday according to locale. I wonder
whether we should change to match that? Maybe "TMD" should act that
way? It's already the case that their "Day" acts like our "TMDay",
evidently.

Either way, though, the WW weeks don't line up with the D weeks,
and we're not likely to make them do so.

So I think an acceptable version of this feature has to involve
defining at least one new format code and maybe as many as three,
to produce year, week and day values that agree on whichever
definition of "a week" you want to use, and then to_date has to
enforce that input uses matching year/week/day field types,
very much like it already does for ISO versus Gregorian dates.

I also notice that neither patch touches the documentation.
A minimum requirement here is defining what you think the underlying
"week" is, if it's neither ISO nor the existing WW definition.
As I said before, it'd also be a good idea to provide some
evidence that there are other people using that same week definition.

regards, tom lane