Skip site navigation (1) Skip section navigation (2)

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
Message-ID: 3279.1111908362@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

In response to

Responses

pgsql-bugs by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group