Search Postgresql Archives

Re: extract (dow/week from date)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux