Date-Based Sorting
Problem
You want to sort rows in temporal order.
Solution
Sort using a date or time data type. If some parts of the values are irrelevant for the sort that you want to accomplish, ignore them.
Discussion
Many database tables include date or time information and it’s
very often necessary to sort results in temporal order. MySQL knows
how to sort temporal data types, so there’s no special trick to
ordering values in
DATE
, DATETIME
, TIME
, or TIMESTAMP
columns. Let’s 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 | 1980-01-01 02:00:00 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 |
+------------+---------------------+----------+---------------------+
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 | 1985-01-01 05:00:00 | | 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 ...
Get MySQL Cookbook, 2nd Edition 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.