Order by datetime does not sort correctly

[Presto-X]

Hello everyone,

 

I have a query that I'm trying to sort by date and time so the newest order should be at the top of the page, but it is not working correctly it's showing all of the orders from today, but it's not sorting the times correctly.

 

The mySQL column type is datetime.

 

Here is my query:

SELECT * FROM orders ORDER BY created DESC

 

What is get is this:

2012-04-26 11:44:09

2012-04-26 01:18:43

2012-04-26 01:17:38

What I want is this:

2012-04-26 01:18:43

2012-04-26 01:17:38

2012-04-26 11:44:09

[requinix]

But that is being sorted correctly. 11am versus 1am.

[Jessica]

Yeah what you're asking for doesn't make sense, it's not in order.

[Presto-X]

Sigh, you are correct, I'm having PHP create the date when the order is added to the database, I think the PHP time is set for 12 hours vs 24 hours.  Because the last two orders 01:17:38 and 01:18:43 where placed in the afternoon so they should have been 13:17:38 and 13:18:43

[Presto-X]

So I updated my PHP date code that adds the order to the database, change the date format from "Y-m-d h:i:s" to "Y-m-d H:i:s", it's working now, thanks

[requinix]

If you're using the current date and time you can just use the NOW() MySQL function.

INSERT INTO orders (..., created, ...) VALUES (..., NOW(), ...)