Robert Creager <[EMAIL PROTECTED]> writes:
> "Dirk Raetzel" <[EMAIL PROTECTED]> confessed:
>> date_trunc('week', ...) returns the wrong week for first days in January if
>> their calendar week belongs to the previous week.
> I brought this up a couple of weeks ago in Hackers since I created this error
> last year :-(
I don't recall seeing that ... anyway, the problem seems to be that
timestamp_trunc implements this as
case DTK_WEEK:
isoweek2date(date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday),
&(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
fsec = 0;
break;
which looks plausible on its face ... but given 2005-01-01, date2isoweek
returns 53 --- which represents the 53rd week of 2004, which is correct
--- and then isoweek2date thinks it is supposed to compute the 53rd week
of 2005, which is not what's wanted.
We need to change the function APIs so that date2isoweek passes back
some indication of which year it thought the week belongs to, and then
isoweek2date must use that instead of the original year number.
Each of these functions is used in several places, so the change is not
quite trivial, but still not a big deal. Who wants to fix it?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
