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

Mark Lorenz <postgres(at)four-two(dot)de> writes:
> 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)

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