There were two hints by readers about this yearweek issue at the 7.4.8 manual: http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html The first by Daniel Grace <graced AT monroe.wednet.edu> 21 May 2004 0:39:19 CREATE OR REPLACE FUNCTION yearweek(TIMESTAMP WITH TIME ZONE) RETURNS INT LANGUAGE plpgsql IMMUTABLE STRICT AS ' DECLARE t TIMESTAMP; BEGIN t := $1::date - EXTRACT(dow FROM $1::date)::int; RETURN EXTRACT(year FROM t)*100 + EXTRACT(week FROM t); END;'; And the second by Wolfgang Diestelkamp <wolfgang AT dndata.de> 09 Mar 2005 15:44:05 CREATE OR REPLACE FUNCTION yearweek(TIMESTAMP) RETURNS INT LANGUAGE plpgsql IMMUTABLE STRICT AS ' DECLARE t TIMESTAMP; d INTEGER; BEGIN d := EXTRACT(dow FROM $1::date)::int; t := $1::date - CASE WHEN d = 0 THEN 6 ELSE d - 1 END; RETURN EXTRACT(year FROM t)*100 + EXTRACT(week FROM t); END;'; While this second attempt improved on the iso day of the week it was broken about the year. Here is the comparison of my yearweek () function and the previous two. The isodow () uses the construct suggested by Tom Lane. create or replace function isodow (timestamp with time zone) returns int language plpgsql immutable strict as ' declare begin return (extract (dow from $1) +6)::int % 7; end;'; create or replace function yearweek (timestamp with time zone) returns int language plpgsql immutable strict as ' declare date timestamp with time zone = $1; fyear integer; begin fyear := extract (year from date - ((isodow (date) -3)::text || \' day\')::interval); return fyear * 100 + extract (week from date); end;'; drop table dates; create table dates (date timestamp); insert into dates values ('1990-01-01'); insert into dates values ('1990-12-31'); insert into dates values ('1991-01-01'); insert into dates values ('1991-12-31'); insert into dates values ('1992-01-01'); insert into dates values ('1992-12-31'); insert into dates values ('1993-01-01'); insert into dates values ('1993-12-31'); insert into dates values ('1994-01-01'); insert into dates values ('1994-12-31'); insert into dates values ('1995-01-01'); insert into dates values ('1995-12-31'); insert into dates values ('1996-01-01'); insert into dates values ('1996-12-31'); insert into dates values ('1997-01-01'); insert into dates values ('1997-12-31'); insert into dates values ('1998-01-01'); insert into dates values ('1998-12-31'); insert into dates values ('1999-01-01'); insert into dates values ('1999-12-31'); insert into dates values ('2000-01-01'); insert into dates values ('2000-12-31'); insert into dates values ('2001-01-01'); insert into dates values ('2001-12-31'); insert into dates values ('2002-01-01'); insert into dates values ('2002-12-31'); insert into dates values ('2003-01-01'); insert into dates values ('2003-12-31'); insert into dates values ('2004-01-01'); insert into dates values ('2004-12-31'); insert into dates values ('2005-01-01'); insert into dates values ('2005-01-02'); insert into dates values ('2005-01-03'); insert into dates values ('2005-01-04'); insert into dates values ('2005-01-05'); insert into dates values ('2005-01-06'); insert into dates values ('2005-01-07'); insert into dates values ('2005-01-08'); insert into dates values ('2005-01-09'); select date, to_char (date, 'Dy') as cday, isodow (date) as isod, yearweek (date) as yw_Clo, yearweek_Daniel (date) as yw_Dan, yearweek_Wolfgang (date) as yw_Wol from dates where yearweek (date) <> yearweek_Wolfgang (date) order by date; date | cday | isod | yw_clo | yw_dan | yw_wol ---------------------+------+------+--------+--------+-------- 1990-12-31 00:00:00 | Mon | 0 | 199101 | 199052 | 199001 1991-01-01 00:00:00 | Tue | 1 | 199101 | 199052 | 199001 1991-12-31 00:00:00 | Tue | 1 | 199201 | 199152 | 199101 1992-01-01 00:00:00 | Wed | 2 | 199201 | 199152 | 199101 1996-12-31 00:00:00 | Tue | 1 | 199701 | 199652 | 199601 1997-01-01 00:00:00 | Wed | 2 | 199701 | 199652 | 199601 1997-12-31 00:00:00 | Wed | 2 | 199801 | 199752 | 199701 1998-01-01 00:00:00 | Thu | 3 | 199801 | 199752 | 199701 2001-12-31 00:00:00 | Mon | 0 | 200201 | 200152 | 200101 2002-01-01 00:00:00 | Tue | 1 | 200201 | 200152 | 200101 2002-12-31 00:00:00 | Tue | 1 | 200301 | 200252 | 200201 2003-01-01 00:00:00 | Wed | 2 | 200301 | 200252 | 200201 2003-12-31 00:00:00 | Wed | 2 | 200401 | 200352 | 200301 2004-01-01 00:00:00 | Thu | 3 | 200401 | 200352 | 200301 (14 rows) Regards, Clodoaldo Pinto ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
