Find overlapping meetings by date/time

Question

I'm working on a calendar app that allows double booking and overlapping meetings to be entered. We're trying to build a web page that shows all overlapping meetings so they can be resolved manually (cancelled, rescheduled, ignored, etc.).

Just to be clear, we WANT to allow conflicting dates to be entered. What we're trying to do is find all of them after they were entered.

Here's a stripped down copy of my meetings table:

CREATE TABLE `meetings` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `is_all_day` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
    `date` DATE NOT NULL,
    `start_time` TIME NULL DEFAULT NULL,
    `end_time` TIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
);


INSERT INTO `meetings` (`id`, `is_all_day`, `date`, `start_time`, `end_time`) VALUES
    (1, NULL, '2021-08-08', '06:00:00', '09:00:00'),
    (2, NULL, '2021-08-09', '06:00:00', '09:00:00'),
    (3, NULL, '2021-08-10', '06:00:00', '09:00:00'),
    (4, NULL, '2021-08-11', '06:00:00', '09:00:00'),
    (5, NULL, '2021-08-12', '06:00:00', '09:00:00'),
    (6, NULL, '2021-08-13', '06:00:00', '09:00:00'),
    (7, NULL, '2021-08-14', '06:00:00', '09:00:00'),
    (8, NULL, '2021-08-10', '08:00:00', '08:30:00'),
    (9, NULL, '2021-08-11', '08:00:00', '08:30:00'),
    (10, NULL, '2021-08-12', '08:00:00', '08:30:00'),
    (11, NULL, '2021-08-10', '06:45:00', '08:45:00'),
    (12, 1, '2021-08-12', NULL, NULL),
    (13, 1, '2021-08-14', NULL, NULL);

Meetings can only occur on a single day between 12am and 11:59am, in other words they won't run into the next day (e.g. 10pm to 2am). The is_all_day column is a boolean flag for if the meeting runs all day.

If there are 2 or more meetings in the database that overlap each other, I would expect the query to return all of them. It's not important to know which meeting(s) they overlap with but if possible, that would be great.

With this data, I'd expect all rows to be returned except the first two, as they don't overlap any other meetings. Here's a visual:

I feel like this is relatively simple but my brain is just out of gas and I don't know where to start. I'd appreciate any push in the right direction!

SQL Fiddle: http://sqlfiddle.com/#!9/44ae09/1

Does this answer your question? [Check overlap of date ranges in MySQL](https://stackoverflow.com/questions/2545947/check-overlap-of-date-ranges-in-mysql) — Progman, Aug 05 '21 at 19:29
Almost, unless I'm missing something there, I don't have an input date - looking for overlaps that are already in the system. — Wesley Murch, Aug 05 '21 at 19:32
We DO want conflicts to be entered, what I'm trying to do here is find them later. Make sense? — Wesley Murch, Aug 05 '21 at 19:38
@Progman if my question is truly the same as the linked one, would you help me understand what I'm missing? I haven't posted here in years and I'm happy to make any clarifications or edits necessary. I have doubt that posting my erroneous attempts to solve this would add any value or clarity. — Wesley Murch, Aug 05 '21 at 19:46
@radocaw No they cannot. It's difficult to say if that's a design flaw or a feature, but it is what it is. — Wesley Murch, Aug 05 '21 at 19:58
@radocaw I really appreciate the help. My sample data may not be ideal, but there is an `is_all_day` flag that may need to come into play. I'll test this out right now, thanks! — Wesley Murch, Aug 05 '21 at 20:03
@radocaw if you want credit feel free to post as an answer and I'll accept it, this got me over the hump and I really appreciate it. Now on to the rest of the conflict checking (meetings have multiple rooms, amenities, etc. that need to be checked as well but that's my problem, plus it's almost time to clock out). Cheerz <3 — Wesley Murch, Aug 05 '21 at 20:19
Have try reboot and hold shift tab while windows screen logo views>>? — stormdrain, Mar 06 '22 at 17:35

score 2 · Accepted Answer · answered Aug 05 '21 at 20:24

For example:

Schema (MySQL v5.7)

CREATE TABLE `meetings` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `is_all_day` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
    `date` DATE NOT NULL,
    `start_time` TIME NULL DEFAULT NULL,
    `end_time` TIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `meetings` (`id`, `is_all_day`, `date`, `start_time`, `end_time`) VALUES
    (1, NULL, '2021-08-08', '06:00:00', '09:00:00'),
    (2, NULL, '2021-08-09', '06:00:00', '09:00:00'),
    (3, NULL, '2021-08-10', '06:00:00', '09:00:00'),
    (4, NULL, '2021-08-11', '06:00:00', '09:00:00'),
    (5, NULL, '2021-08-12', '06:00:00', '09:00:00'),
    (6, NULL, '2021-08-13', '06:00:00', '09:00:00'),
    (7, NULL, '2021-08-14', '06:00:00', '09:00:00'),
    (8, NULL, '2021-08-10', '08:00:00', '08:30:00'),
    (9, NULL, '2021-08-11', '08:00:00', '08:30:00'),
    (10, NULL, '2021-08-12', '08:00:00', '08:30:00'),
    (11, NULL, '2021-08-10', '06:45:00', '08:45:00'),
    (12, 1, '2021-08-12', NULL, NULL),
    (13, 1, '2021-08-14', NULL, NULL);

Query #1

select distinct x.* 
  from meetings x
  JOIN meetings y
    ON y.id <> x.id
   and y.date = x.date
   and y.start_time < x.end_time
   and y.end_time >= x.start_time
 order
    by id;

id	date	start_time	end_time
3	2021-08-10	06:00:00	09:00:00
4	2021-08-11	06:00:00	09:00:00
5	2021-08-12	06:00:00	09:00:00
8	2021-08-10	08:00:00	08:30:00
9	2021-08-11	08:00:00	08:30:00
10	2021-08-12	08:00:00	08:30:00
11	2021-08-10	06:45:00	08:45:00

The test for 'all day meeting' is left as an exercise for the reader View on DB Fiddle

Find overlapping meetings by date/time

1 Answers1