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

Tom Lane wrote:
> 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

I don't remember seeing it either.

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

I have developed a patch to fix the problem. Instead of changing the
API, I added code to decrement the year when the week number was 53 and
the month was January. It corrected the problem:

test=> select date_trunc('week', timestamp '2005-01-01');
date_trunc
---------------------
2004-12-27 00:00:00
(1 row)

test=> select date_trunc('week', timestamptz '2005-01-01');
date_trunc
------------------------
2004-12-27 00:00:00-05
(1 row)

test=> select date_trunc('week', date '2005-01-01');
date_trunc
------------------------
2004-12-27 00:00:00-05
(1 row)

It seems the idea of returning the week number and assuming the year is
the same is fundamentally flawed, but the user API is that way so I am
not inclined to adjust the server API at this point.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073