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.
| 
No comments:
Post a Comment