Re: extract (dow/week from date) - Mailing list pgsql-general

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