Group data by datetime periods in MySQL

-- ---------------------------------------------------------------------------
-- Group data by datetime periods
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=106
-- ---------------------------------------------------------------------------

-- 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.