Recipe 7.5. 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 | 1980-01-01 02:00:00 | | 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 | | 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 | +------------+---------------------+----------+---------------------+ mysql> SELECT * FROM temporal_val ORDER BY dt; +------------+---------------------+----------+---------------------+ | d          | dt                  | t        | ts                  | +------------+---------------------+----------+---------------------+ | 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 | | 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 | | 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 | +------------+---------------------+----------+---------------------+ mysql> SELECT * FROM temporal_val ORDER BY t; +------------+---------------------+----------+---------------------+ | d          | dt                  | t        | ts                  | +------------+---------------------+----------+---------------------+ | 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 | | 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 | | 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 | +------------+---------------------+----------+---------------------+ mysql> SELECT * FROM temporal_val ORDER BY ts; +------------+---------------------+----------+---------------------+ | d          | dt                  | t        | ts                  | +------------+---------------------+----------+---------------------+ | 1981-01-01 | 1871-01-01 12:00:00 | 03:00:00 | 1975-01-01 04:00:00 | | 1970-01-01 | 1884-01-01 12:00:00 | 13:00:00 | 1980-01-01 02:00:00 | | 1964-01-01 | 1899-01-01 12:00:00 | 01:00:00 | 1985-01-01 05:00:00 | | 1999-01-01 | 1860-01-01 12:00:00 | 19:00:00 | 2021-01-01 03:00:00 | +------------+---------------------+----------+---------------------+ 

Sometimes a temporal sort uses only part of a date or time column. In that case, use an expression that extracts the part or parts you need and sort the result using the expression. Some examples of this are given in the next few recipes.