MySQL Cookbook, 2nd Edition

Date-Based Summaries

Problem

You want to produce a summary based on date or time values.

Solution

Use GROUP BY to place temporal values into categories of the appropriate duration. Often this involves using expressions to extract the significant parts of dates or times.

Discussion

To put rows in time order, use an ORDER BY clause to sort a column that has a temporal type. If instead you want to summarize rows based on groupings into time intervals, you need to determine how to categorize each row into the proper interval and use GROUP BY to group them accordingly.

For example, to determine how many drivers were on the road and how many miles were driven each day, group the rows in the driver_log table by date:

mysql>SELECT trav_date,
    -> COUNT(*) AS 'number of drivers', SUM(miles) As 'miles logged'
    -> FROM driver_log GROUP BY trav_date;
+------------+-------------------+--------------+
| trav_date  | number of drivers | miles logged |
+------------+-------------------+--------------+
| 2006-08-26 |                 1 | 115          |
| 2006-08-27 |                 1 | 96           |
| 2006-08-29 |                 3 | 822          |
| 2006-08-30 |                 2 | 355          |
| 2006-09-01 |                 1 | 197          |
| 2006-09-02 |                 2 | 581          |
+------------+-------------------+--------------+

However, this summary will grow lengthier as you add more rows to the table. At some point, the number of distinct dates likely will become so large that the summary fails to be useful, and you’d probably decide to change the category size from daily to weekly or monthly.

When a temporal column contains so many distinct ...