CODEWITHSUNDEEP
mysql
S_R
S_R

Reputation: 1998

MySQL get NOT overlapping date ranges

I have seen so many questions similar to this, but they all seem to be tailored to highlighting when date ranges are overlapping, I need the opposite.

Lets say I have a table like so

id| start_date | end_date   | room_id
1 | 15/05/2018 | 30/06/2020 | 1
2 | 01/11/2018 | 31/10/2019 | 2
3 | 01/08/2020 | 31/07/2022 | 1
4 | 01/12/2019 | 30/11/2021 | 2
5 | 01/08/2020 | 31/07/2022 | 3

As you can see there are multiple bookings for each room. I need to be able to specify either a single start/end date or both, and get back what DOESN'T overlap (i.e, the available rooms)

For example, if i specified just a start date of 01/05/2018 then every room will return, or if i specify just an end date of 30/07/2020 then every room will return because neither of those dates are between the start and end date of each booking. Even though id 1 has a booking that ends on 30/06/2020 and a new one that starts on 01/08/2020, it would still be available because someone could book between those 2 dates.

If I specified both start and end dates, it searches through and returns only the rooms that have no bookings between the 2 dates at all.

I have read plenty of questions online and the logic seems to be

SELECT *
FROM bookings
WHERE $start_date < expiry_date AND $end_date > start_date

which i understand, but if I ran this query above with the following dates

SELECT *
FROM bookings
WHERE '2018-10-01' < expiry_date AND '2019-10-01' > start_date

it returns

id| start_date | end_date   | room_id
1 | 15/05/2018 | 30/06/2020 | 1
2 | 01/11/2018 | 31/10/2019 | 2

How do I get it so that when I pass either a start date, end date or BOTH it returns the rooms that are available?

Upvotes: 0

Views: 322

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

By De Morgan's Laws, we can negate the overlapping range query you gave as follows:

SELECT *
FROM bookings
WHERE $start_date >= expiry_date OR $end_date <= start_date;

The expression ~(P ^ Q) is equivalent to ~P V ~Q.

Upvotes: 2

Related Questions