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

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