Query to check if a given range of date doesn't fits between multiple range of dates

Question

I have a table with 2 columns, checkinDate and checkoutDate. What I have to do is to add a range in the table only if it doesn't overlap the others ranges. How can I know if a given range of dates fits between all those ranges with a query?

For example, from the following rows:

startDate   -   endDate
2019-12-10  -   2019-12-15
2019-12-16  -   2019-12-22
2019-12-29  -   2019-01-05
2020-01-20  -   2020-01-25

If the given range of date goes from 2019-12-23 to 2019-12-28, it doesn't overlap the others ranges so I can add it in the table.

But if the range goes from from 2019-12-23 to 2019-12-30, it overlap a range so I cannot add it in the table.

I know how to check the range line by line but not how to check it with the entire table.

Answer 1

Here is a simple way to check for date overlap in an insert query

insert into mytable(startDate, endDate)
select i.startDate, i.endDate
from (select '2019-12-23' startDate, '2019-12-30' endDate) i
where not exists (
    select 1 
    from mytable t 
    where t.startDate <= i.endDate and t.endDate >= i.startDate
)

The date range to insert is declared in the subquery aliased i. If any record in the table overlaps with that range, the insert is skipped, else it happens.

Demo on DB Fiddle:

-- set up
CREATE TABLE mytable(
   id int auto_increment primary key
   ,startDate DATE  NOT NULL 
  ,endDate   DATE  NOT NULL
);
INSERT INTO mytable(startDate,endDate) VALUES ('2019-12-10','2019-12-15');
INSERT INTO mytable(startDate,endDate) VALUES ('2019-12-16','2019-12-22');
INSERT INTO mytable(startDate,endDate) VALUES ('2019-12-29','2019-01-05');
INSERT INTO mytable(startDate,endDate) VALUES ('2020-01-20','2020-01-25');

-- initial table content
select * from mytable order by startDate

id | startDate  | endDate   
-: | :--------- | :---------
 1 | 2019-12-10 | 2019-12-15
 2 | 2019-12-16 | 2019-12-22
 3 | 2019-12-29 | 2019-01-05
 4 | 2020-01-20 | 2020-01-25

-- this range does not overlap
insert into mytable(startDate, endDate)
select i.startDate, i.endDate
from (select '2019-12-23' startDate, '2019-12-30' endDate) i
where not exists (
    select 1 
    from mytable t 
    where t.startDate <= i.endDate and t.endDate >= i.startDate
)

-- confirm it was inserted
select * from mytable order by id

id | startDate  | endDate   
-: | :--------- | :---------
 1 | 2019-12-10 | 2019-12-15
 2 | 2019-12-16 | 2019-12-22
 3 | 2019-12-29 | 2019-01-05
 4 | 2020-01-20 | 2020-01-25
 5 | 2019-12-23 | 2019-12-30

-- this range overlaps
insert into mytable(startDate, endDate)
select i.startDate, i.endDate
from (select '2019-12-23' startDate, '2019-12-28' endDate) i
where not exists (
    select 1 
    from mytable t 
    where t.startDate <= i.endDate and t.endDate >= i.startDate
)

-- it was not inserted
select * from mytable order by id

id | startDate  | endDate   
-: | :--------- | :---------
 1 | 2019-12-10 | 2019-12-15
 2 | 2019-12-16 | 2019-12-22
 3 | 2019-12-29 | 2019-01-05
 4 | 2020-01-20 | 2020-01-25
 5 | 2019-12-23 | 2019-12-30