How to order by time stamp in mysql?

Question

SELECT * FROM table ORDER BY timestamp;

When i'm trying to order the records by its timestamp i'm getting the following order

08/20/2012 02:09:39 PM
08/20/2012 03:19:08 PM
08/20/2012 09:04:24 AM
08/20/2012 09:05:25 AM

How to change the query so that the records are ordered from AM to PM?

Answer 1

The problem is that your string representation of the timestamp is not in canonical format, that is, sorting the string value does not sort in timestamp order.

To get the rows sorted in order, you can convert the character representation of the value into a DATETIME or TIMESTAMP datatype, or at least into a character representation in a canonical format (e.g. 'YYYY-MM-DD hh:mm:ss' with a 24 hour clock).

The STR_TO_DATE function is useful for converting a string representation in a known format into DATETIME:

SELECT * FROM table
ORDER BY STR_TO_DATE(`timestamp`,'%m/%d/%Y %h:%i:%s %p')

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

Answer 2

You will want to use STR_TO_DATE()

select *
from dates
order by STR_TO_DATE(dt,'%m/%d/%Y %h:%i:%s') desc

See SQL Fiddle with demo