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

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