When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530),
"Kumar" <sgnerd@yahoo.com.sg> confessed:
> Dear Friends,
>
> Postgres 7.3.4 on RH Linux7.2.
>
> While this works for month and why not for week
>
date_trunc (obviously) doesn't support week. I ran into this a while ago, and
came up with this function. I left the function signature the same as
date_trunc, even though I don't use the first argument. I did only minor
testing (10 years or so), so no guarantee about it's correctness. And it's kind
of slow...
CREATE OR REPLACE FUNCTION date_trunc_week( text, timestamp )
RETURNS timestamp AS '
DECLARE reading_time ALIAS FOR $2; year timestamp; dow integer; adjust text; week text;
BEGIN year := date_trunc( ''year''::text, reading_time ); week := date_part( ''week'', reading_time ) - 1 || ''
week''; dow := date_part( ''dow'', year ); -- If the dow is less than Thursday, then the start week is last year IF
dow<= 4 THEN adjust := 1 - dow || '' day''; ELSE adjust := 8 - dow || '' day''; END IF; RETURN year +
adjust::interval+ week::interval;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
-- 05:37:49 up 1 day, 13:20, 2 users, load average: 0.09, 0.36, 0.63
Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003