MySQL Cookbook

6.8 Date-Based Sorting

6.8.1 Problem

You want to sort in temporal order.

6.8.2 Solution

Sort using a date or time column type, ignoring parts of the values that are irrelevant if necessary.

6.8.3 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 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
+------------+---------------------+----------+----------------+
mysql> SELECT * FROM temporal_val ORDER BY dt;
+------------+---------------------+----------+----------------+
| d          | dt                  | t        | ts             |
+------------+---------------------+----------+----------------+
| 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 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
+------------+---------------------+----------+----------------+
mysql> SELECT * FROM temporal_val ORDER BY t;
+------------+---------------------+----------+----------------+
| d          | dt                  | t        | ts             |
+------------+---------------------+----------+----------------+
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 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 |
+------------+---------------------+----------+----------------+
mysql> SELECT * FROM temporal_val ORDER BY ts;
+------------+---------------------+----------+----------------+
| d          | dt                  | t        | ts             |
+------------+---------------------+----------+----------------+
| 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 19750101040000 |
| 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 19800101020000 |
| 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 19850101050000 |
| 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 20210101030000 |
+------------+---------------------+----------+----------------+

Sometimes a temporal sort uses only part of a date or time column. In that case, you can bust out the part or parts you need and use them to order the results. Some examples of this are given in the next few sections.