OK. I believe the following function provides the correct functionality. Agree/disagree? If it's good, I'll figure out how to convert this little monster to 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
Thanks for your input on this Kurt.
Cheers,
Rob
--
21:48:49 up 48 days, 3:05, 4 users, load average: 3.80, 3.13, 2.82
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004
pgp7qKDfwTHuS.pgp
Description: PGP signature
