Sort table by date

Question

I have a question about how I can get recent purchases by date and time. I tried to:

$stmt = $this->pdo->prepare("SELECT * FROM `website_transactions` ORDER BY `transaction_DATE` AND `transaction_HOUR` DESC LIMIT 8");
$stmt->execute();

He was going to sort by time and date, but he’s just ordering by date, so the result is messy.

the time is in 24 hours format, in case of 00 until 23 and the date in Year-Month-Day format

Answer 1

The problem is the use of the logical operator AND unduly:

ORDER BY `transaction_DATE` AND `transaction_HOUR` DESC LIMIT 8
                            ^^^

If you want to sort by more than one column, separate by comma, always starting from the order with more priority to the smallest:

ORDER BY expressao1, expressao2, expressao3

Applying to your case:

SELECT * FROM `website_transactions` ORDER BY `transaction_DATE`, `transaction_HOUR` LIMIT 8

If you want to reverse the order, you need to put the DESC (Descending) in all columns as applicable. Example:

nome ASC, transaction_DATE DESC, transaction_HOUR DESC

In this example, the name would be in alphabetical order (crescent), but the date in descending order and the time also (the word ASC is dispensable, I put only to illustrate)

Remembering that there are formats in Mysql that store date and time, but the convenience of using or not these formats depends a lot on how you will use and index the data.

Answer 2

Why didn’t you keep the DESC for the two camps?

It is ordering by "asc date" and only "desc time". Do so:

ORDER BY `transaction_DATE` DESC, `transaction_HOUR` DESC

Answer 3

A more ideal way would be to leave only one column to store this information... Using the type TIMESTAMP for the column.

Try modifying your table like this:

ALTER TABLE website_transactions DROP COLUMN transaction_DATE;
ALTER TABLE website_transactions DROP COLUMN transaction_HOUR;
ALTER TABLE website_transactions ADD transaction_date TIMESTAMP;

Then you could use this much simpler code to perform your query

SELECT * FROM `website_transactions` ORDER BY `transaction_date` DESC LIMIT 8