Date-Based Sorting
Problem
You want to sort in temporal order.
Solution
Sort using a date or time column type, ignoring parts of the values that are irrelevant if necessary.
Discussion
Many types of information include date or time information and
it’s very often necessary to sort results in
temporal order. MySQL knows how to sort temporal column types, so
there’s no special trick to ordering values in
DATE
, DATETIME
,
TIME
, or TIMESTAMP
columns.
Begin with a table that contains values for each of those types:
mysql> SELECT * FROM temporal_val;
+------------+---------------------+----------+----------------+
| d | dt | t | ts |
+------------+---------------------+----------+----------------+
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
+------------+---------------------+----------+----------------+
Using an ORDER
BY
clause with
any of these columns sorts the values into the appropriate order:
mysql> SELECT * FROM temporal_val ORDER BY d;
+------------+---------------------+----------+----------------+ | d | dt | t | ts | +------------+---------------------+----------+----------------+ | 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 | | 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 | | 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.