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

The following bug has been logged online:

Bug reference: 1563
Logged by: Dirk Raetzel
Email address: d00273(at)spaetzle(dot)de
PostgreSQL version: 8.0.1
Operating system: i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)
Description: wrong week returnded by date_trunc('week', ...)
Details:

date_trunc('week', ...) returns the wrong week for first days in January if
their calendar week belongs to the previous week.

Example:
select date_trunc('week', timestamp '2005-01-01');
gives: 2006-01-02 00:00:00 but 2004-12-27 00:00:00 would be right.

sample code:

create table weektest (
date timestamp
);

copy weektest from STDIN;
'1999-01-01'
'2000-01-01'
'2001-01-01'
'2002-01-01'
'2003-01-01'
'2004-01-01'
'2005-01-01'
'2006-01-01'
'2007-01-01'
'2008-01-01'
'2009-01-01'
\.

date | week_t | week_p
---------------------+---------------------+--------
1999-01-01 00:00:00 | 2000-01-03 00:00:00 | 53
2000-01-01 00:00:00 | 2000-12-25 00:00:00 | 52
2001-01-01 00:00:00 | 2001-01-01 00:00:00 | 1
2002-01-01 00:00:00 | 2001-12-31 00:00:00 | 1
2003-01-01 00:00:00 | 2002-12-30 00:00:00 | 1
2004-01-01 00:00:00 | 2003-12-29 00:00:00 | 1
2005-01-01 00:00:00 | 2006-01-02 00:00:00 | 53
2006-01-01 00:00:00 | 2006-12-25 00:00:00 | 52
2007-01-01 00:00:00 | 2007-01-01 00:00:00 | 1
2008-01-01 00:00:00 | 2007-12-31 00:00:00 | 1
2009-01-01 00:00:00 | 2008-12-29 00:00:00 | 1
(11 rows)

When grilled further on (Fri, 25 Mar 2005 08:28:58 +0000 (GMT)),
"Dirk Raetzel" <d00273(at)spaetzle(dot)de> confessed:

>
> The following bug has been logged online:
>
> Bug reference: 1563
> Logged by: Dirk Raetzel
> Email address: d00273(at)spaetzle(dot)de
> PostgreSQL version: 8.0.1
> Operating system: i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)
> Description: wrong week returnded by date_trunc('week', ...)
> Details:
> 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 :-( Never got feedback on whether the following function solved the
problem correctly or not. If you would agree this works, then I'll see about
moving it into C.

CREATE OR REPLACE FUNCTION
date_trunc_week(timestamp without time zone)
RETURNS timestamp without time zone
AS '
DECLARE
reading_time ALIAS FOR $1;
year timestamp;
dow integer;
temp interval;
weeks text;
adjust text;
BEGIN
year := date_trunc( ''year''::text, reading_time );
dow := date_part( ''dow'', year );
IF dow >= 4 THEN
adjust := 1 - dow || '' day'';
ELSIF dow != 1 THEN
adjust := dow - 6 || '' day'';
ELSE
adjust := ''0 day'';
END IF;
temp := reading_time - (year + adjust::interval);
weeks := trunc(date_part( ''days'', temp ) / 7) || '' weeks'';
RETURN year + adjust::interval + weeks::interval;
END;
' LANGUAGE plpgsql;

select date_trunc_week( '2004-01-01' ); -- 2003-12-29 00:00:00
select date_trunc_week( '2005-01-01' ); -- 2004-12-27 00:00:00
select date_trunc_week( '2005-06-01' ); -- 2005-05-30 00:00:00
select date_trunc_week( '2006-01-01' ); -- 2005-12-26 00:00:00
select date_trunc_week( '2007-01-01' ); -- 2007-01-01 00:00:00

--
22:56:20 up 9 days, 2:46, 7 users, load average: 4.72, 5.79, 4.76
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

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

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

When grilled further on (Sun, 27 Mar 2005 02:26:02 -0500),
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> confessed:

>
> 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'll take a look at it next weekend (4/2 or 4/3) if it's still not done.

Cheers,
Rob

--
20:43:32 up 14:07, 9 users, load average: 3.15, 2.76, 2.00
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

On Mon, 28 Mar 2005, Bruce Momjian wrote:

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

The problem arises as well when week number is 52 as in '2006-01-01'.

Dirk

When grilled further on (Tue, 29 Mar 2005 07:43:53 +0200),
Dirk Raetzel <d00273(at)spaetzle(dot)de> confessed:

>
>
> On Mon, 28 Mar 2005, Bruce Momjian wrote:
>
> > 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:
>
> The problem arises as well when week number is 52 as in '2006-01-01'.

Try these at the least:

select date_trunc_week( '2004-01-01' ); -- should be 2003-12-29 00:00:00
select date_trunc_week( '2005-01-01' ); -- should be 2004-12-27 00:00:00
select date_trunc_week( '2005-06-01' ); -- should be 2005-05-30 00:00:00
select date_trunc_week( '2006-01-01' ); -- should be 2005-12-26 00:00:00
select date_trunc_week( '2007-01-01' ); -- should be 2007-01-01 00:00:00

If it helps, look earlier in this thread for a (overly complicated?) version in
plpgsql.

Cheers,
Rob

--
06:39:48 up 1 day, 3 min, 9 users, load average: 0.10, 0.24, 0.30
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

Robert Creager wrote:
-- Start of PGP signed section.
> When grilled further on (Tue, 29 Mar 2005 07:43:53 +0200),
> Dirk Raetzel <d00273(at)spaetzle(dot)de> confessed:
>
> >
> >
> > On Mon, 28 Mar 2005, Bruce Momjian wrote:
> >
> > > 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:
> >
> > The problem arises as well when week number is 52 as in '2006-01-01'.
>
> Try these at the least:
>
> select date_trunc_week( '2004-01-01' ); -- should be 2003-12-29 00:00:00
> select date_trunc_week( '2005-01-01' ); -- should be 2004-12-27 00:00:00
> select date_trunc_week( '2005-06-01' ); -- should be 2005-05-30 00:00:00
> select date_trunc_week( '2006-01-01' ); -- should be 2005-12-26 00:00:00
> select date_trunc_week( '2007-01-01' ); -- should be 2007-01-01 00:00:00
>
> If it helps, look earlier in this thread for a (overly complicated?) version in
> plpgsql.

Wow, an early January date can be part of the 52nd week of the previous
year too.

Here is a new patch, with a documentation mention.

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