Re: Getting the week of a date

--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530),
"Kumar" <sgnerd(at)yahoo(dot)com(dot)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

--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAkAwuvwACgkQLQ/DKuwDYzm2xACdGIajt5kT/heTMhmiyIqa63Gb
9isAn2WgpI2A2GLiOaLFLbyjjZMmy6JS
=Hhtd
-----END PGP SIGNATURE-----

--Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG--