The extract (dow from date) function returns 0 for Sunday (nice).
My problem is that Sunday is the last day of the week according to
extract (week from date). Is it the expected behavior?
teste=# create table dates (date timestamp);
CREATE TABLE
teste=# insert into dates values ('2005-08-08');
INSERT 0 1
teste=# insert into dates values ('2005-08-09');
INSERT 0 1
teste=# insert into dates values ('2005-08-10');
INSERT 0 1
teste=# insert into dates values ('2005-08-11');
INSERT 0 1
teste=# insert into dates values ('2005-08-12');
INSERT 0 1
teste=# insert into dates values ('2005-08-13');
INSERT 0 1
teste=# insert into dates values ('2005-08-14');
INSERT 0 1
teste=# select date, extract (week from date) as week, extract (dow
from date) as dow
teste-# from dates
teste-# order by date;
date | week | dow
---------------------+------+-----
2005-08-08 00:00:00 | 32 | 1
2005-08-09 00:00:00 | 32 | 2
2005-08-10 00:00:00 | 32 | 3
2005-08-11 00:00:00 | 32 | 4
2005-08-12 00:00:00 | 32 | 5
2005-08-13 00:00:00 | 32 | 6
2005-08-14 00:00:00 | 32 | 0
(7 rows)
In mysql the date functions work as I need it:
order by yearweek(day, 2) desc, dayofweek(day);
Regards,
Clodoaldo Pinto
