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

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

From: Robert Creager <Robert_Creager(at)LogicalChaos(dot)org>
To: "Dirk Raetzel" <d00273(at)spaetzle(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1563: wrong week returnded by date_trunc('week',
Date: 2005-03-27 06:00:53
Message-ID: 20050326230053.7ee629fa@thunder.logicalchaos.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-patches
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

In response to

Responses

pgsql-bugs by date

Next:From: Michael FuhrDate: 2005-03-27 06:22:49
Subject: Re: BUG #1555: bug in GROUP BY?
Previous:From: Oliver JowettDate: 2005-03-27 05:54:16
Subject: Re: BUG #1561: wrong detection of number of parameters in

pgsql-patches by date

Next:From: Tom LaneDate: 2005-03-27 07:26:02
Subject: Re: BUG #1563: wrong week returnded by date_trunc('week',
Previous:From: Bruce MomjianDate: 2005-03-26 17:59:48
Subject: Re: [PATCHES] problem with CR+LF in files in psql \i command

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