What is the proper date and time format for ordering rows in MySQL/PHP?

I'm trying to SELECT rows from a MySQL table and order them by date and time:

mysql_query("SELECT * FROM `user` ORDER BY post_date DESC");

The post_date is formatted as such: 2012-07-18 01:52:57am (o-m-d h:i:sa). I figured that this was the proper simple date format, however it isn't sorting accurately.

Ex Output:

1.) 2012-07-18 12:41:07am

2.) 2012-07-18 12:26:03am

3.) 2012-07-18 01:52:57am

As you can see the most recent post at 1:52 is at the bottom when it should be at the top. What the heck am I doing wrong and what is the correct date and time format?

Answers


I think it is treating 12>1 ..better use 24 hrs format without am/pm


SELECT * FROM `user` 
ORDER BY cast(post_date as datetime) DESC

Are you storing post_date as a string? There is a datetime type (also timestamp, probably preferred for something like the post date). If you can change to these types, it will work better.

Otherwise, you'll have to stort them in php using something like strtotime:

$results = array();
while ($row = mysql_fetch_assoc($result)) {
   $results[strtotime($row['post_date'])] = $row;
}
ksort($results);

This is the simplest solution, but it also requires that no timestamps are the same.


Need Your Help

Cost of multiple instantiations

c# instantiation

While working in a project today, I came across the following code: