Uh, you are ordering by 'date', not column 3, try ORDER BY 3.
---------------------------------------------------------------------------
Clodoaldo Pinto wrote:
> 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
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@xxxxxxxxxxxxxxxx | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
