## 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 2022 