October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You want to sort in temporal order.
Sort using a date or time column type, ignoring parts of the values that are irrelevant if necessary.
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 ...