Group data by datetime periods
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.
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.