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...
