From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Clodoaldo Pinto <clodoaldo(dot)pinto(at)gmail(dot)com> |
Cc: | "pgsql-general postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: extract (dow/week from date) |
Date: | 2005-08-21 01:03:32 |
Message-ID: | 200508210103.j7L13Wc26435@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-08-21 01:28:33 | Re: selecting rows older than X, ensuring index is used |
Previous Message | Clodoaldo Pinto | 2005-08-21 00:46:19 | extract (dow/week from date) |