MySQL count records by a group of time chunks

count_by_timechunk.sql

-- It's often super useful to generate a timseries-based histogram
-- of records counts over a period time, grouped into arbitrary 
-- timedelta chunks.

-- Imagine we have a table like this.
-- It has a datetime field, dt, 
-- some special key somekey (imagine its a foreign key or 
-- some other useful value that you want to filter by),
-- and an index on (somekey, dt) 
CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime(6),
  `somekey` bigint(20),
  KEY `somekey_dt` (`somekey`,`dt`),
) ENGINE=InnoDB

-- We can make a nice timeseries of record counts
-- grouped into whatever resolution of time we want.
-- Here we count the last 24 hours of records grouped 
-- into 1 hour chunks
SELECT
    somekey,
    dt,
    COUNT(*)
FROM
    foo
WHERE
        dt >= DATE_SUB(NOW(), INTERVAL 1 DAY)
    AND somekey = 59
GROUP BY 
    UNIX_TIMESTAMP(dt) DIV (60*60)
ASC
;

-- +---------+----------------------------+----------+
-- | somekey | exec_end_dt                | COUNT(*) |
-- +---------+----------------------------+----------+
-- |      59 | 2021-10-12 21:35:27.778295 |       53 |
-- |      59 | 2021-10-12 22:05:37.916914 |       53 |
-- |      59 | 2021-10-12 23:00:16.538500 |       26 |
-- |      59 | 2021-10-13 00:05:55.169213 |       21 |
-- |      59 | 2021-10-13 01:16:30.664940 |        3 |
-- |      59 | 2021-10-13 02:02:31.638007 |       21 |
-- |      59 | 2021-10-13 03:02:02.481255 |       16 |
-- |      59 | 2021-10-13 04:22:07.292576 |       18 |
-- |      59 | 2021-10-13 07:14:17.088551 |        1 |
-- |      59 | 2021-10-13 08:34:19.811483 |        3 |
-- |      59 | 2021-10-13 09:04:45.636517 |       59 |
-- |      59 | 2021-10-13 10:04:58.425337 |       40 |
-- |      59 | 2021-10-13 11:05:05.321121 |       47 |
-- |      59 | 2021-10-13 12:05:15.595055 |       83 |
-- |      59 | 2021-10-13 13:05:35.593189 |      125 |
-- |      59 | 2021-10-13 14:05:52.227853 |      179 |
-- |      59 | 2021-10-13 15:06:03.017374 |      111 |
-- |      59 | 2021-10-13 16:06:21.172513 |       55 |
-- |      59 | 2021-10-13 17:06:31.921137 |       31 |
-- |      59 | 2021-10-13 18:06:46.076815 |       57 |
-- |      59 | 2021-10-13 19:06:54.877986 |       78 |
-- |      59 | 2021-10-13 20:06:53.275727 |       50 |
-- |      59 | 2021-10-13 21:00:44.097591 |       49 |
-- +---------+----------------------------+----------+
-- 23 rows in set (0.00 sec)