Re: extract (dow/week from date)

2005/8/21, Tom Lane <tgl@sss.pgh.pa.us>:
> Stephan Szabo <sszabo@megazone.bigpanda.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