1 year ago
#385917

Tobia
Find overlapping date-range records with Mysql 5.6?
I have a list of events with date-range and venue id. I need to find the max count of distinct active venues at the same time.
create table if not exists events
(
eventstart datetime,
eventend datetime,
venue int
);
INSERT INTO events VALUES ('2022-04-06 18:00:00', '2022-04-06 19:30:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:15:00', '2022-04-06 21:00:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:45:00', '2022-04-06 20:00:00', 2);
INSERT INTO events VALUES ('2022-04-06 18:30:00', '2022-04-06 19:00:00', 3);
https://www.db-fiddle.com/f/a2PRXG2WQ3samrWb66fimv/4
With the previous example, I expect a result of 3 because from 18.45-19.00 there 3 concurrent distinct venues active at the same time.
I cannot find a smart way to write this query.
mysql
date-range
mysql-5.6
0 Answers
Your Answer