I have a table with date ranges like the following:
start | end
2020-07-25 20:37:00 2020-07-25 20:44:00
2020-07-25 21:37:00 2020-07-25 22:44:00
2020-07-26 07:11:00 2020-07-27 10:50:00
...
At the end, I want a histogram which shows for every hour of a day how many date ranges "overlaps" each hour. So the resulting histogram consists of 24 bars.
How do I do this in SQL for MySQL? (Side note: I'm using TypeORM, but I'm able to write plain SQL statements)
I only found solutions calculating and grouping by the length of the individual intervals with TIMESTAMPDIFF, but that's not what I want to achieve.
In future I may want to show the same histogram not per hour but per minute of a day or per day of a month and so on. But I assume that's simple to do once I get the idea of the query :)
One method is the brute force method:
with recursive hours as (
select 0 as hh
union all
select hh + 1
from hours
where hh < 23
)
select h.hh, count(t.start)
from hours h left join
t
on start >= '2020-07-25' + interval h.hh hour and
end < '2020-07-25' + interval (h.hh + 1) hour
where end < '2020-07-25' + interval 1 day and
start >= '2020-07-25'
group by h.hh
order by h.hh;