Order By Date Question

In response to BlackMage <dsd7872@uncw.edu>:

I have a question about ordering by date. I have a table with two fields and
some date

Name(character varying) | Event_Date(timestamp with timezone)
A | 2009-09-10 5:30:00
B | 2009-09-10- 00:00:00
C | 2009-09-11 17:30:00
D | 2009-09-11 07:30:00

I want to order by date and then by name, so I want the result A,B,C,D. The
problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date, DESC',
it includes the actual time (HH:MM:SS) so the order comes out B,A,D,C.

So what I am asking is how do I order only by the date? YYYY-MM-DD?

You could do "ORDER BY event_date::DATE"

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to BlackMage :

I have a question about ordering by date. I have a table with two fields and
some date

Name(character varying) | Event_Date(timestamp with timezone)
A | 2009-09-10 5:30:00
B | 2009-09-10- 00:00:00
C | 2009-09-11 17:30:00
D | 2009-09-11 07:30:00

I want to order by date and then by name, so I want the result A,B,C,D. The
problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date, DESC',
it includes the actual time (HH:MM:SS) so the order comes out B,A,D,C.

Cast it to DATE, for instance:

... order by Event_Date::date desc

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)

On Tue, Sep 08, 2009 at 06:29:28AM -0700, BlackMage wrote:

I want to order by date and then by name, so I want the result A,B,C,D. The
problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date, DESC',
it includes the actual time (HH:MM:SS) so the order comes out B,A,D,C.

So what I am asking is how do I order only by the date? YYYY-MM-DD?

Casting to a date first is probably easiest, date_trunc would also work;
so one of:

ORDER BY Event_Date::DATE, Name;
or
ORDER BY date_trunc('day',Event_Date), Name;

--
Sam http://samason.me.uk/

On Tuesday 08 September 2009 6:29:28 am BlackMage wrote:

I have a question about ordering by date. I have a table with two fields
and some date

Name(character varying) | Event_Date(timestamp with timezone)
A | 2009-09-10 5:30:00
B | 2009-09-10- 00:00:00
C | 2009-09-11 17:30:00
D | 2009-09-11 07:30:00

I want to order by date and then by name, so I want the result A,B,C,D. The
problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date,
DESC', it includes the actual time (HH:MM:SS) so the order comes out
B,A,D,C.

So what I am asking is how do I order only by the date? YYYY-MM-DD?
--
View this message in context:
http://www.nabble.com/Order-By-Date-Question-tp25346259p25346259.html Sent
from the PostgreSQL - general mailing list archive at Nabble.com.

Cast the timestamp to date:

SELECT * FROM table_name ORDER BY Event_Date::date DESC

Though to guarantee the order you want you will have to do:

SELECT * FROM table_name ORDER BY Event_Date::date, Name
--
Adrian Klaver
aklaver@comcast.net

On Tue, Sep 08, 2009 at 06:29:28AM -0700, BlackMage wrote:

I have a question about ordering by date. I have a table with two fields and
some date

Name(character varying) | Event_Date(timestamp with timezone)
A | 2009-09-10 5:30:00
B | 2009-09-10- 00:00:00
C | 2009-09-11 17:30:00
D | 2009-09-11 07:30:00

I want to order by date and then by name, so I want the result A,B,C,D. The
problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date, DESC',
it includes the actual time (HH:MM:SS) so the order comes out B,A,D,C.

So what I am asking is how do I order only by the date? YYYY-MM-DD?

Because you're using timestamp with time zone, you need to tread
carefully, as casting is full of scare. One way to tread carefully is
to

ORDER BY date_trunc('day', "Event_Date"), "Name"

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate