Group data by datetime periods

from the Artful Common Queries page

To group rows by a time period whose length in minutes divides evenly into 60, use this formula: 
GROUP BY 
  ((60/periodMinutes) * HOUR( thistime ) + 
   FLOOR( MINUTE( thistime ) / periodMinutes )
  )
where thistime is the TIME column and periodMinutes is the period length in minutes. So to group by 15-min periods, write ... 
SELECT ...
GROUP BY 
  ( 4 * HOUR(thistime) + 
    FLOOR(MINUTE(thistime)/15)
  )
...
A simpler application of the same logic works for hours. For example, list data by three-hour periods: 
DROP TABLE IF EXISTS t;
CREATE TABLE t(t time,i int);
INSERT INTO t VALUES
('01:01:01',1),('02:02:02',2),('05:05:05',5);
SELECT 
  FLOOR(HOUR(t)/3) AS period, 
  GROUP_CONCAT(i) AS i
FROM t
GROUP BY period;
+--------+------+
| period | i    |
+--------+------+
|      0 | 1,2  |
|      1 | 5    |
+--------+------+
And the same logic works for months ... 
GROUP BY 
  ((12/periodMonths) * YEAR( thisdate ) + 
  FLOOR( MONTH( thisdate ) / periodMonths ))
It could be made to work for weeks with a function that maps the results of WEEK() to the range 1...52. When the desired grouping period is a value returned by a MySQL date-time function, matters become simpler: just group by the desired value. Thus to group by weeks, write .. 
SELECT ...
GROUP BY WEEK( datecol)
...
You can also Group By an expression like 
  CEIL( TIME_TO_SEC( TIMEDIFF( timestamp1, timestamp2 )) / (60*60) )
modifying the denominator to suit. For example, in the rental table of the Sakila test database, how does rental duration (TIMEDIFF(return_date, rental_date)) distribute? Rounding up to days, this query ... 
SELECT
  CEILING( HOUR( TIMEDIFF(return_date, rental_date) )/24 ) as Days,
  COUNT(*)
FROM rental
WHERE return_date IS NOT NULL
GROUP BY Days
ORDER BY Days;
... shows rental durations from one to ten days. How realistic is the sakila DB, for example do customers keep R-rated films longer? 
SELECT
  CEILING( HOUR( TIMEDIFF(return_date, rental_date) ) / 24 ) as 'Days',
  COUNT(*),
  ROUND( 100* SUM( IF(f.rating='R',1,0) ) / COUNT(*), 0 ) as 'Pct R-rated'
FROM rental    r
JOIN inventory i ON r.inventory_id=i.inventory_id
JOIN film      f ON i.film_id=f.film_id
WHERE r.return_date IS NOT NULL
GROUP BY Days
ORDER BY Days;
+------+----------+-------------+
| Days | COUNT(*) | Pct R-rated |
+------+----------+-------------+
|    1 |      776 |          23 |
|    2 |     1408 |          20 |
|    3 |     1333 |          21 |
|    4 |     1333 |          20 |
|    5 |     1352 |          19 |
|    6 |     1375 |          20 |
|    7 |     1380 |          18 |
|    8 |     1432 |          21 |
|    9 |     1370 |          19 |
|   10 |      571 |          21 |
+------+----------+-------------+
No, they're not; sakila renters seem to be asexual.

If there is no expression invoking a MySQL date-time function that returns the desired grouping period, you will need to write your own stored function.

http://www.agcross.com/2014/11/resample-mysql-datetime-interval/ lays out a more baroque approach using From_UnixTime(), Unix_Timestamp() and integer division.

Last updated 4 May 2024