CODEWITHSUNDEEP
phpmysqlsqldatetimestamp
user7128548
user7128548

Reputation:

Wrong ORDER BY with MySQL and PHP

In my MySQL there's a column named date, type is timestamp.

Using this in my PHP scrip:

$stmt = $pdo->prepare('SELECT id, date FROM my_table ORDER BY DATE(date) ASC, TIME(date) DESC');

output is:

2016-11-26 16:55:30
2016-11-26 16:53:08
2016-11-26 16:37:25
2016-11-26 16:32:29
2016-11-26 16:18:57
2016-11-28 19:37:37

But that's the wrong order. I want to have the newest date on top, including date and time.

Means:

2016-11-28 19:37:37
2016-11-26 16:55:30
2016-11-26 16:53:08
2016-11-26 16:37:25
2016-11-26 16:32:29
2016-11-26 16:18:57

What am I doing wrong?

Upvotes: 0

Views: 122

Answers (2)

Rahul
Rahul

Reputation: 77906

Why can't you just have order by on the entire column value rather than sorting the date part and time part separately

ORDER BY `date` DESC

Upvotes: 2

Max
Max

Reputation: 891

SELECT id, date FROM my_table ORDER BY date DESC

Upvotes: 1

Related Questions