Re: extract (dow/week from date)

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