Re: BUG #1563: wrong week returnded by date_trunc('week',

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Creager <Robert_Creager(at)LogicalChaos(dot)org>
Cc: "Dirk Raetzel" <d00273(at)spaetzle(dot)de>, pgsql-bugs(at)postgresql(dot)org, Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
Subject: Re: BUG #1563: wrong week returnded by date_trunc('week',
Date: 2005-03-27 07:26:02
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-patches

Robert Creager <Robert_Creager(at)LogicalChaos(dot)org> writes:
> "Dirk Raetzel" <d00273(at)spaetzle(dot)de> 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;

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

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-03-27 07:39:09 Re: foreign key constraint not working when index tablespace is not default.
Previous Message Stephan Szabo 2005-03-27 07:02:37 Re: BUG #1555: bug in GROUP BY?

Browse pgsql-patches by date

  From Date Subject
Next Message Jim C. Nasby 2005-03-27 21:24:15 Re: [PATCHES] Approximate count(*)
Previous Message Robert Creager 2005-03-27 06:00:53 Re: BUG #1563: wrong week returnded by date_trunc('week',