If the format isn't too important, you can return two columns for the interval. You might even just need the start of the interval, which can be determined by:
date_format(created_at - interval minute(created_at)%30 minute, '%H:%i') as period_start
the alias can be used in GROUP BY and ORDER BY clauses. If you also need the end of the interval, you will need a small modification:
SELECT
date_format(created_at - interval minute(created_at)%30 minute, '%H:%i') as period_start,
date_format(created_at + interval 30-minute(created_at)%30 minute, '%H:%i') as period_end,
COUNT(*)
FROM urls
GROUP BY period_start
ORDER BY period_start ASC;
Of course you can also concatenate the values:
SELECT concat_ws('-',
date_format(created_at - interval minute(created_at)%30 minute, '%H:%i'),
date_format(created_at + interval 30-minute(created_at)%30 minute, '%H:%i')
) as period,
COUNT(*)
FROM urls
GROUP BY period
ORDER BY period ASC;
Demo: http://rextester.com/RPN50688
Another thing, is that, if it there is half an hour with no results, I
would like it to return 0
If you use the result in a procedural language, you can initialize all 48 rows with zero in a loop and then "inject" the non-zero rows from the result.
However - If you need it to be done in SQL, you will need a table for a LEFT JOIN with at least 48 rows. That could be done inline with a "huge" UNION ALL statement, but (IMHO) it would be ugly. So I prefer to have sequence table with one integer column, which can be very usefull for reports. To create that table I usually use the information_schema.COLUMNS, since it is available on any MySQL server and has at least a couple of hundreds rows. If you need more rows - just join it with itself.
Now let's create that table:
drop table if exists helper_seq;
create table helper_seq (seq smallint auto_increment primary key)
select null
from information_schema.COLUMNS c1
, information_schema.COLUMNS c2
limit 100; -- adjust as needed
Now we have a table with integers from 1 to 100 (though right now you only need 48 - but this is for demonstration).
Using that table we can now create all 48 time intervals:
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;
We will get the following result:
period_start | period_end
00:00:00 | 00:30:00
00:30:00 | 01:00:00
...
23:30:00 | 24:00:00
Demo: http://rextester.com/ISQSU31450
Now we can use it as a derived table (subquery in FROM clause) and LEFT JOIN your urls table:
select p.period_start, p.period_end, 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
Demo: http://rextester.com/IQYQ32927
Last step (if really needed) is to format the result. We can use CONCAT or CONCAT_WS and TIME_FORMAT in the outer select. The final query would be:
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
The result would look like:
period | cnt
00:00-00:30 | 1
00:30-01:00 | 0
...
23:30-24:00 | 3
Demo: http://rextester.com/LLZ41445
