MySQL Cookbook

6.9 Sorting by Calendar Day

6.9.1 Problem

You want to sort by day of the calendar year.

6.9.2 Solution

Sort using the month and day of a date, ignoring the year.

6.9.3 Discussion

Sorting in calendar order differs from sorting by date. You ignore the year part of the dates and sort using only the month and day to order records in terms of where they fall during the calendar year. Suppose you have an event table that looks like this when values are ordered by actual date of occurrence:

mysql> SELECT date, description FROM event ORDER BY date;
+------------+-------------------------------------+
| date       | description                         |
+------------+-------------------------------------+
| 1215-06-15 | Signing of the Magna Carta          |
| 1732-02-22 | George Washington's birthday        |
| 1776-07-14 | Bastille Day                        |
| 1789-07-04 | US Independence Day                 |
| 1809-02-12 | Abraham Lincoln's birthday          |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1944-06-06 | D-Day at Normandy Beaches           |
| 1957-10-04 | Sputnik launch date                 |
| 1958-01-31 | Explorer 1 launch date              |
| 1989-11-09 | Opening of the Berlin Wall          |
+------------+-------------------------------------+

To put these items in calendar order, sort them by month, then by day within month:

mysql> SELECT date, description FROM event
    -> ORDER BY MONTH(date), DAYOFMONTH(date);
+------------+-------------------------------------+
| date       | description                         |
+------------+-------------------------------------+
| 1958-01-31 | Explorer 1 launch date              |
| 1809-02-12 | Abraham Lincoln's birthday          |
| 1732-02-22 | George Washington's birthday        |
| 1944-06-06 | D-Day at Normandy Beaches           |
| 1215-06-15 | Signing of the Magna Carta          |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day                 |
| 1776-07-14 | Bastille Day                        |
| 1957-10-04 | Sputnik launch date                 |
| 1989-11-09 | Opening of the Berlin Wall          |
+------------+-------------------------------------+

MySQL also has a DAYOFYEAR( ) function that you might think would be useful for calendar day sorting:

mysql> SELECT date, description FROM event ORDER BY DAYOFYEAR(date);
+------------+-------------------------------------+
| date       | description                         |
+------------+-------------------------------------+
| 1958-01-31 | Explorer 1 launch date              |
| 1809-02-12 | Abraham Lincoln's birthday          |
| 1732-02-22 | George Washington's birthday        |
| 1944-06-06 | D-Day at Normandy Beaches           |
| 1215-06-15 | Signing of the Magna Carta          |
| 1919-06-28 | Signing of the Treaty of Versailles |
| 1789-07-04 | US Independence Day                 |
| 1776-07-14 | Bastille Day                        |
| 1957-10-04 | Sputnik launch date                 |
| 1989-11-09 | Opening of the Berlin Wall          |
+------------+-------------------------------------+

That appears to work, but only because the table doesn't have records in it that expose a problem with the use of DAYOFYEAR( ): It can generate the same value for different calendar days. For example, February 29 of leap years and March 1 of non-leap years appear to be the same day:

mysql> SELECT DAYOFYEAR('1996-02-29'), DAYOFYEAR('1997-03-01');
+-------------------------+-------------------------+
| DAYOFYEAR('1996-02-29') | DAYOFYEAR('1997-03-01') |
+-------------------------+-------------------------+
|                      60 |                      60 |
+-------------------------+-------------------------+

This property means that DAYOFYEAR( ) won't necessarily produce correct results for calendar sorting. It can group dates together that actually occur on different calendar days.

If a table represents dates using separate year, month, and day columns, calendar sorting requires no date-part extraction. Just sort the relevant columns directly. For example, the master ballplayer table from the baseball1.com database distribution represents names and birth dates as follows:

mysql> SELECT lastname, firstname, birthyear, birthmonth, birthday
    -> FROM master;
+----------------+--------------+-----------+------------+----------+
| lastname       | firstname    | birthyear | birthmonth | birthday |
+----------------+--------------+-----------+------------+----------+
| AARON          | HANK         |      1934 |          2 |        5 |
| AARON          | TOMMIE       |      1939 |          8 |        5 |
| AASE           | DON          |      1954 |          9 |        8 |
| ABAD           | ANDY         |      1972 |          8 |       25 |
| ABADIE         | JOHN         |      1854 |         11 |        4 |
| ABBATICCHIO    | ED           |      1877 |          4 |       15 |
| ABBEY          | BERT         |      1869 |         11 |       29 |
| ABBEY          | CHARLIE      |      1866 |         10 |       14 |
...

To sort those records in calendar order, use the birthmonth and birthday columns. Of course, that will leave records unsorted within any given day, so you may also want to add additional sort columns. The following query selects players with known birthdays, sorts them by calendar order, and by name for each calendar day:

mysql> SELECT lastname, firstname, birthyear, birthmonth, birthday
    -> FROM master
    -> WHERE birthmonth IS NOT NULL AND birthday IS NOT NULL
    -> ORDER BY birthmonth, birthday, lastname, firstname;
+----------------+--------------+-----------+------------+----------+
| lastname       | firstname    | birthyear | birthmonth | birthday |
+----------------+--------------+-----------+------------+----------+
| ALLEN          | ETHAN        |      1904 |          1 |        1 |
| BEIRNE         | KEVIN        |      1974 |          1 |        1 |
| BELL           | RUDY         |      1881 |          1 |        1 |
| BERTHRONG      | HARRY        |      1844 |          1 |        1 |
| BETHEA         | BILL         |      1942 |          1 |        1 |
| BISHOP         | CHARLIE      |      1924 |          1 |        1 |
| BOBB           | RANDY        |      1948 |          1 |        1 |
| BRUCKMILLER    | ANDY         |      1882 |          1 |        1 |
...

For large datasets, sorting using separate date part columns can be much faster than sorts based on extracting pieces of DATE values. There's no overhead for part extraction, but more important, you can index the date part columns separately—something not possible with a DATE column.