so-22283244-result-formatted, MySQL

so-22283244-result-formatted

Language: Layout:

drop table if exists urls;
create table urls (created_at timestamp);
insert into urls(created_at)values
    ('2018-03-02 12:00:00'),
    ('2018-03-02 12:10:20'),
    ('2018-03-02 12:30:00'),
    ('2018-03-02 12:50:00'),
    ('2018-03-02 13:00:00'),
    ('2018-03-02 15:31:00'),
    ('2018-03-02 18:18:18'),
    ('2018-03-02 23:33:32');

drop table if exists helper_seq;
create table helper_seq (seq smallint auto_increment primary key)
    select null as seq
    from information_schema.COLUMNS c1
       , information_schema.COLUMNS c2
    limit 100;

select concat_ws('-',
         time_format(p.period_start, '%H:%i'),
         time_format(p.period_end,   '%H:%i')
       ) as period,
       count(u.created_at) as cnt
from (
    select time(0) + interval 30*(seq-1) minute as period_start,
           time(0) + interval 30*(seq)   minute as period_end
    from helper_seq s
    where s.seq <= 48
) p
left join urls u
    on  time(u.created_at) >= p.period_start
    and time(u.created_at) <  p.period_end
group by p.period_start, p.period_end
order by p.period_start


absolute service time: 0,46 sec

	period	cnt
1	00:00-00:30	0
2	00:30-01:00	0
3	01:00-01:30	0
4	01:30-02:00	0
5	02:00-02:30	0
6	02:30-03:00	0
7	03:00-03:30	0
8	03:30-04:00	0
9	04:00-04:30	0
10	04:30-05:00	0
11	05:00-05:30	0
12	05:30-06:00	0
13	06:00-06:30	0
14	06:30-07:00	0
15	07:00-07:30	0
16	07:30-08:00	0
17	08:00-08:30	0
18	08:30-09:00	0
19	09:00-09:30	0
20	09:30-10:00	0
21	10:00-10:30	0
22	10:30-11:00	0
23	11:00-11:30	0
24	11:30-12:00	0
25	12:00-12:30	2
26	12:30-13:00	2
27	13:00-13:30	1
28	13:30-14:00	0
29	14:00-14:30	0
30	14:30-15:00	0
31	15:00-15:30	0
32	15:30-16:00	1
33	16:00-16:30	0
34	16:30-17:00	0
35	17:00-17:30	0
36	17:30-18:00	0
37	18:00-18:30	1
38	18:30-19:00	0
39	19:00-19:30	0
40	19:30-20:00	0
41	20:00-20:30	0
42	20:30-21:00	0
43	21:00-21:30	0
44	21:30-22:00	0
45	22:00-22:30	0
46	22:30-23:00	0
47	23:00-23:30	0
48	23:30-24:00	1