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

Hi,

I apologize for the mistake.

For the mailing list correspondence I created this mail account. But I
forgot to change the sender name. So, the "postgres" name appeared as
sender name in the mailing list. I changed it.

Kind regards,
Mark/S-Man42

> Hi,
>
> some days ago I ran into a problem with the to_date() function. I
> originally described it on StackExchange:
> https://dba.stackexchange.com/questions/250111/unexpected-behaviour-for-to-date-with-week-number-and-week-day
>
> The problem:
>
> If you want to parse a date string with year, week and day of week,
> you can do this using the ISO week pattern: 'IYYY-IW-ID'. This works
> as expected:
>
> date string | to_date()
> ------------+------------
> '2019-1-1' | 2018-12-31 -> Monday of the first week of the year
> (defined as the week that includes the 4th of January)
> '2019-1-2' | 2019-01-01
> '2019-1-3' | 2019-01-02
> '2019-1-4' | 2019-01-03
> '2019-1-5' | 2019-01-04
> '2019-1-6' | 2019-01-05
> '2019-1-7' | 2019-01-06
>
> '2019-2-1' | 2019-01-07
> '2019-2-2' | 2019-01-08
>
> But if you are trying this with the non-ISO pattern 'YYYY-WW-D', the
> result was not expected:
>
> date string | to_date()
> -------------------------
> '2019-1-1' | 2019-01-01
> '2019-1-2' | 2019-01-01
> '2019-1-3' | 2019-01-01
> '2019-1-4' | 2019-01-01
> '2019-1-5' | 2019-01-01
> '2019-1-6' | 2019-01-01
> '2019-1-7' | 2019-01-01
>
> '2019-2-1' | 2019-01-08
> '2019-2-2' | 2019-01-08
>
> As you can see, the 'D' part of the pattern doesn't influence the
> resulting date.
>
> The answer of Laurenz Albe pointed to a part of the documentation, I
> missed so far:
>
> "In to_timestamp and to_date, weekday names or numbers (DAY, D, and
> related field types) are accepted but are ignored for purposes of
> computing the result. The same is true for quarter (Q) fields."
> (https://www.postgresql.org/docs/12/functions-formatting.html)
>
> So, I had a look at the relevant code part. I decided to try a patch
> by myself. Now it works as I would expect it:
>
> date string | to_date()
> -------------------------
> '2019-1-1' | 2018-12-30 -> Sunday (!) of the first week of the year
> (the first week is at the first day of year)
> '2019-1-2' | 2018-12-31
> '2019-1-3' | 2019-01-01
> '2019-1-4' | 2019-01-02
> '2019-1-5' | 2019-01-03
> '2019-1-6' | 2019-01-04
> '2019-1-7' | 2019-01-05
>
> '2019-2-1' | 2019-01-06
> '2019-2-2' | 2019-01-07
>
> Furthermore, if you left the 'D' part, the date would be always set to
> the first day of the corresponding week (in that case it is Sunday, in
> contrast to the ISO week, which starts mondays).
>
> To be consistent, I added similar code for the week of month pattern
> ('W'). So, using the pattern 'YYYY-MM-W-D' yields in:
>
> date string | to_date()
> ---------------------------
> '2018-12-5-1' | 2018-12-23
> '2018-12-6-1' | 2018-12-30
> '2019-1-1-1' | 2018-12-30 -> First day (Su) of the first week of the
> first month of the year
> '2019-2-2-1' | 2019-02-03 -> First day (Su) of the second week of
> February
> '2019-10-3-5' | 2019-10-17 -> Fifth day (Th) of the third week of
> October
>
> If you left the 'D', it would be set to 1 as well.
>
> The code can be seen here:
> https://github.com/S-Man42/postgres/commit/534e6bd70e23864f385d60ecf187496c7f4387c9
>
> I hope, keeping the code style of the surrounding code (especially the
> ISO code) is ok for you.
>
> Now the questions:
> 1. Although the ignorance of the 'D' pattern is well documented, does
> the new behaviour might be interesting for you?
> 2. Does it work as you'd expect it?
> 3. Because this could be my very first contribution to the PostgreSQL
> code base, I really want you to be as critical as possible. I am not
> quite sure if I didn't miss something important.
> 4. Currently something like '2019-1-8' does not throw an exception but
> results in the same as '2019-2-1' (8th is the same as the 1st of the
> next week). On the other hand, currently, the ISO week conversion
> gives out the result of '2019-1-7' for every 'D' >= 7. I am not sure
> if this is better. I think a consistent exception handling should be
> discussed separately (date roll over vs. out of range exception vs.
> ISO week behaviour)
>
> So far, I am very curious about your opinions!
>
> Kind regards,
> Mark/S-Man42