[WIP] MySQLでcreated_atを持つレコードを5分間隔で集計する

mysql> create table event (created_at timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into event values ('2018-01-01 00:01:00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into event values ('2018-01-01 00:02:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into event values ('2018-01-01 00:03:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into event values ('2018-01-01 00:04:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into event values ('2018-01-01 00:05:00');
Query OK, 1 row affected (0.00 sec)

mysql> select concat(date_format(created_at, '%Y-%m-%d %H:'), lpad(truncate(date_format(created_at, '%i') / 5, 0) * 5, 2, '0')) as created_at__interval_5, count(*) from event group by 1 order by 1;
+------------------------+----------+
| created_at__interval_5 | count(*) |
+------------------------+----------+
| 2018-01-01 00:00       |        4 |
| 2018-01-01 00:05       |        1 |
+------------------------+----------+
2 rows in set (0.00 sec)

説明

TBD