Обсуждение: Order By Date Question
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.
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