MySQL- Find records with overlapping period

I have a table hotel_booking

reference	start_date	numberOfDays
ABC	11-08-2021	2
DEF	12-08-2021	2
GHI	13-08-2021	3
JKL	10-08-2021	2

So the first task I wanted was to get all bookings which has startDate between x and y date which is straight forward query

select reference
from hotel_booking
where DATE(start_date) BETWEEN '2021-08-11' AND '2021-08-12'

This returns me ABC and DEF which is perfect.

Now I want to check for all booking that have a overlapping period based on numberOfdays

So for example get me all the bookings that are within the period mentioned by startDate and endDate based on numberOfDays

INPUT: For example if startDate is 11-08-2021 and endDate is 12-08-2021 then

Expected Output: the bookings returned are JKL, ABC and DEF since JKL has booking for 2 days which will overlap with startDate 11-08-2021

Any pointer will be appreciated

Answer

Since you’re not saving end date you need to calculate it as start_date + interval numberOfDays days to get the exclusive end date. The date overlap query would look like:

SELECT *
FROM t
WHERE @d2 > start_date AND (start_date + interval numberOfDays day) > @d1
-- In order to check [2021-08-11, 2021-08-12]
-- You will set @d1 and @d2 to 2021-08-11 and 2021-08-13

For completeness, here is the inclusive end date version:

SELECT *
FROM t
WHERE @d2 >= start_date AND (start_date + interval numberOfDays - 1 day) >= @d1
-- In order to check [2021-08-11, 2021-08-12]
-- You will set @d1 and @d2 to 2021-08-11 and 2021-08-12