MySQL Cookbook, 2nd Edition

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 ...