Thread: 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@spaetzle.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@spaetzle.de> confessed:

>
> The following bug has been logged online:
>
> Bug reference:      1563
> Logged by:          Dirk Raetzel
> Email address:      d00273@spaetzle.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@LogicalChaos.org> writes:
> "Dirk Raetzel" <d00273@spaetzle.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

When grilled further on (Sun, 27 Mar 2005 02:26:02 -0500),
Tom Lane <tgl@sss.pgh.pa.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