Recipe 7.6. Sorting by Calendar Day
ProblemYou want to sort by day of the calendar year. SolutionSort using the month and day of date values, ignoring the year. DiscussionSorting in calendar order differs from sorting by date. You need to ignore the year part of the dates and sort using only the month and day to order rows in terms of where they fall during the calendar year. Suppose that 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, and 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 suspect 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 rows in it that expose a problem with using DAYOFYEAR( ) for sorting: it can generate the same value for different calendar days. For example, February 29 of leap years and March 1 of nonleap years have the same day-of-year value: 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 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 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 separatelysomething not possible with a DATE column. The principle here is that you should design the table to make it easy to extract or sort by the values that you expect to use a lot. |
