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 |
Message-ID: | 3279.1111908362@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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;
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
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? |
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', |