Re: [GENERAL] Order By Date Question
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? -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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order By Date Question
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? -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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order By Date Question
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? -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 akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order By Date Question
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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order By Date Question
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. > > So what I am asking is how do I order only by the date? -MM-DD? You could do "ORDER BY event_date::DATE" -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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? -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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general