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

Hi,

while preparing the patch for the Commitfest, I found a bug in the
to_char() function that is quite correlated with this issue:

SELECT to_char('1997-02-01'::date, 'YYYY-WW-D')

returns: 1997-05-7 -> which is ok, I believe. Feb, 1st was on Saturday,
so counting from Sundays, it was day 7 of week 5.

SELECT to_char('1997-02-03'::date, 'YYYY-WW-D')

returns: 1997-05-2 -> This cannot be. The input date is two days laters,
but the result is 5 days earlier. I'd expect 1997-06-2 as result, but
this occurs another week later:

SELECT to_char('1997-02-10'::date, 'YYYY-WW-D')

This is wrong, because this should be week 7 instead. On the other hand,
the ISO week formats work very well.

I'll have a look at the code and try to fix it in the patch as well.

Kind regards,
Mark

Am 2019-10-08 17:49, schrieb Mark Lorenz:
> 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