LaVOZs

The World’s Largest Online Community for Developers

'; mysql - SQL Count events with duration per hour - LavOzs.Com

I have data of an event with duration (say, eating a meal at a restaurant) and I want to know for any given hour how many events were taking place. The data looks like this:

Event     |   Start Time    |   End Time
-----------------------------------------
  1       |   12:03         |   14:20
  2       |   12:30         |   12:50
  3       |   13:05         |   14:45
  4       |   14:01         |   14:49

I also have "Duration" available as an alternative to "End Time". The result I'm looking for would be like this:

  Hour     |     Count
-----------------------
   12      |     2
   13      |     2
   14      |     3

During hour 12, there were two events happening (1 & 2), hour 13 also had two events (1 & 3) and hour 14 had three events (1, 3, & 4).

I can do this programmatically with a loop. I can count when the events start (or end) in SQL. But I'd really like to bridge the gap and do this in SQL, but I can't think of a way.

One possible solution (works with MySQL v5.6+ and SQLite3):

create table hours(Hour int);
insert into hours values
  (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
  (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);

create table log(Event int,StartTime varchar(5),EndTime varchar(5));
insert into log values
  (1,'12:03','14:20'),
  (2,'12:30','12:50'),
  (3,'13:05','14:45'),
  (4,'14:01','14:49');

-- ------------------------------------------------------------------------------

select Hour,count(Event) Count
  from log join hours
    on Hour between substr(StartTime,1,2) and substr(EndTime,1,2)
  group by Hour;

If you are running MySQL 8.0, you could use UNION ALL, window functions and aggregation, like so:

select hr, sum(sum(cnt)) over(order by hr) cnt
from (
    select hour(start_time) hr, 1 cnt from mytable
    union all select hour(end_time) + 1, -1 from mytable
) t
group by hr

Demo on DB Fiddle:

hr | cnt
-: | --:
12 |   2
13 |   2
14 |   3
15 |   0

If you do not have MySql 8, then create a table hour:

CREATE TABLE hour (
  hr INT PRIMARY KEY
);

INSERT INTO hour(hr) VALUES
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
    (12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);

And then:

select h.hr, count(*) as cnt from hour h
join mytable m on h.hr between hour(m.Start_Time) and hour(m.End_Time)
group by hr
order by hr
;

See Db-Fiddle

Related
How can I prevent SQL injection in PHP?
Add a column with a default value to an existing table in SQL Server
How to return only the Date from a SQL Server DateTime datatype
MySQL - UPDATE query based on SELECT Query
How do I UPDATE from a SELECT in SQL Server?
Finding duplicate values in a SQL table
SQL Server Group by Count of DateTime Per Hour?
How do I import an SQL file using the command line in MySQL?