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'';
ENDIF; 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
