'Sort Mysql data by nearest date compare to current date

I want to sort data by the earliest date compare to the current date. Like - Now 8 April, so the nearest data from the screenshot is ID 5 (9 April) and then ID 4 (28 April). My expected outcome will - be ID 5, 4, 3, 2, 1 Data will save randomly in the database table. I am using MySQL. Is there any SQL query for this?

Sample screenshot

Thanks

phpmysqlsqldatabaseperformance


Solution 1:[1]

I would suggest using DATEDIFF to get the number of days away from today, as well as ABS to get the exact difference instead of a positive/negative number

ORDER BY ABS(DATEDIFF(NOW(), create_at))

This chart shows what the differences will use to sort

+------------+----------+----------+
| date       | datediff | with_abs |
+------------+----------+----------+
| 2022-03-24 |       14 |       14 |
| 2022-04-09 |       -2 |        2 |
| 2022-04-24 |      -17 |       17 |
+------------+----------+----------+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions