1 year ago

#385917

test-img

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

Accepted video resources