MySQL order by DATETIME field outputting wrong posts order

Question

I’m trying to order my posts by MySQL datetime field So I’m instating data in following format

gmdate('Y-m-d h:i:s');

Below my query

SELECT * FROM posts WHERE post_stats = 1 ORDER BY published_date DESC LIMIT 10

And the posts order is wrong. Then I tried

SELECT * FROM posts WHERE post_stats = 1 ORDER BY DATE(published_date) DESC LIMIT 10

Also tried

SELECT * FROM posts WHERE post_stats = 1 ORDER BY UNIXDATE(published_date) DESC LIMIT 10

Still the posts order is wrong.

EDIT

Ex

my posts should be displayed as

post 2 | 2016-12-20 04:52:08
post 1 | 2016-12-20 04:51:20

but it is displaying as

post 1 | 2016-12-20 04:51:20
post 2 | 2016-12-20 04:52:08

Can someone point me out what I’m doing wrong here? Appreciate your time.

Answer 1

Well seems that changing my date format from 12 hours to 24 hours seems to do the trick

gmdate('Y-m-d h:i:s');

to

gmdate('Y-m-d H:i:s');

Answer 2

a tips how i do so things go with time do a ai in ur database called id and allways order by id :D

Answer 3

Sounds like the DESC is your issue. Try ASC like

SELECT * FROM posts 
WHERE post_stats = 1 
ORDER BY published_date ASC LIMIT 10