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

>> 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.
>
> Why not? These format codes are specified as
>
> D day of the week, Sunday (1) to Saturday (7)
> WW week number of year (1–53) (the first week starts on the first day
> of the year)
>

Because 1997-05-2 is earlier than 1997-05-7. But 1997-02-03 is later
than 1997-02-01. From my point of view, this is confusing.

> I don't see anything there that says that "D" is correlated with "WW".
> We do have a connection between "ID" and "IW", so that ID ought to
> specify a day within an IW week, but there's no connection between "D"
> and either "W" or "WW" week numbering. It's a day of the week, as
> per the regular calendar. Trying to define it as something else is
> just going to break stuff.
>
> The only way to make "D" as it stands compatible with a week-numbering
> system is to ensure that your weeks always start on Sundays, that is,
> just as confusing as ISO weeks but slightly differently confusing.
>
> Perhaps it would be worth inventing format codes that do have the
> same relationship to "W" and/or "WW" as "ID" does to "IW". But
> repurposing "D" for that is a bad idea.
>
> regards, tom lane

I don't want to create any connection here. The day is calculated
correctly. But the week number is wrong. 1997-02-03 was in week number
6, as well as 1997-02-04. But Postgres returns 5. The problem with
to_char() is, that the week number is considering only the nmber of days
in the year and divides them by 7. So, there is no diffence whether the
year starts on Sunday or any other week day. So, an offset is missing,
which yields in wrong week numbers, as I can see...