When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530), "Kumar" <[EMAIL PROTECTED]> 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
pgp00000.pgp
Description: PGP signature
