Re: extract (dow/week from date)

2005/8/21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > I think something like:
> > (CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
>
> It's really not that hard:
>
> (extract(dow from date) + 6) % 7
>
> You can rotate to any week-start day you like by substituting different
> things for "6".
>
> regards, tom lane
>
Not obvious as extract (isodow) but good enough for me. Thanks.

But then i also need to order by year-week the same way mysql's
yearweek (date, 3) so i did:

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,
extract (year from date - cast (((extract (dow from date) +6)::int %
7 -3)::text || ' day' as interval)) as yearweek,
extract (week from date) as eweek,
(extract (dow from date) +6)::int % 7 as edow
from dates
order by date;
date | cday | yearweek | eweek | edow
---------------------+------+----------+-------+------
1990-01-01 00:00:00 | Mon | 1990 | 1 | 0
1990-12-31 00:00:00 | Mon | 1991 | 1 | 0
1991-01-01 00:00:00 | Tue | 1991 | 1 | 1
1991-12-31 00:00:00 | Tue | 1992 | 1 | 1
1992-01-01 00:00:00 | Wed | 1992 | 1 | 2
1992-12-31 00:00:00 | Thu | 1992 | 53 | 3
1993-01-01 00:00:00 | Fri | 1992 | 53 | 4
1993-12-31 00:00:00 | Fri | 1993 | 52 | 4
1994-01-01 00:00:00 | Sat | 1993 | 52 | 5
1994-12-31 00:00:00 | Sat | 1994 | 52 | 5
1995-01-01 00:00:00 | Sun | 1994 | 52 | 6
1995-12-31 00:00:00 | Sun | 1995 | 52 | 6
1996-01-01 00:00:00 | Mon | 1996 | 1 | 0
1996-12-31 00:00:00 | Tue | 1997 | 1 | 1
1997-01-01 00:00:00 | Wed | 1997 | 1 | 2
1997-12-31 00:00:00 | Wed | 1998 | 1 | 2
1998-01-01 00:00:00 | Thu | 1998 | 1 | 3
1998-12-31 00:00:00 | Thu | 1998 | 53 | 3
1999-01-01 00:00:00 | Fri | 1998 | 53 | 4
1999-12-31 00:00:00 | Fri | 1999 | 52 | 4
2000-01-01 00:00:00 | Sat | 1999 | 52 | 5
2000-12-31 00:00:00 | Sun | 2000 | 52 | 6
2001-01-01 00:00:00 | Mon | 2001 | 1 | 0
2001-12-31 00:00:00 | Mon | 2002 | 1 | 0
2002-01-01 00:00:00 | Tue | 2002 | 1 | 1
2002-12-31 00:00:00 | Tue | 2003 | 1 | 1
2003-01-01 00:00:00 | Wed | 2003 | 1 | 2
2003-12-31 00:00:00 | Wed | 2004 | 1 | 2
2004-01-01 00:00:00 | Thu | 2004 | 1 | 3
2004-12-31 00:00:00 | Fri | 2004 | 53 | 4
2005-01-01 00:00:00 | Sat | 2004 | 53 | 5
2005-01-02 00:00:00 | Sun | 2004 | 53 | 6
2005-01-03 00:00:00 | Mon | 2005 | 1 | 0
2005-01-04 00:00:00 | Tue | 2005 | 1 | 1
2005-01-05 00:00:00 | Wed | 2005 | 1 | 2
2005-01-06 00:00:00 | Thu | 2005 | 1 | 3
2005-01-07 00:00:00 | Fri | 2005 | 1 | 4
2005-01-08 00:00:00 | Sat | 2005 | 1 | 5
2005-01-09 00:00:00 | Sun | 2005 | 1 | 6
(39 rows)

I am not sure it is bullet proof.

If no one comes up with something simpler, it looks like extract
(yearweek) would be welcome.

Regards, Clodoaldo Pinto