Re: date_trunc problem in HEAD

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