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