CODEWITHSUNDEEP
mysql
Álvaro Franz
Álvaro Franz

Reputation: 801

MySQL query to check if a part of a dates interval is contained within another dates interval

Given the MySQL table:

occupied_room_dates

id (int)     date_a (DATE)       date_b (DATE)
1            2018-12-20          2018-12-25
5            2019-01-05          2019-02-15
36           2019-01-02          2019-03-21

And given two variables containig date strings in the format yyyy/mm/dd:

DT_A = yyyy/mm/dd (Example: 2019-02-03)
DT_B = yyyy/mm/dd (Example: 2019-05-03)

I want to perform a query to check if for given DT_A and DT_B (being DT_A < DT_B), there is at least one row in the table occupied_room_dates for which the dates intervals:

a) [date_a - date_b]
b) [DT_A - DT_B]

overlap each other.

Examples / Expectation:

With the example, for ROOM_ID = 36, it should return the third row in the example table, since the dates intervals overlap.

For another example (ROOM_ID = 36, DT_A = 2019-06-15, DT_B = 2019-07-15) there should be no match, since there is no dates interval overlaping it.

What I tried so far is:

Since there are four generic ways of the overlap happening, I am creating 4 conditions that will make the alarm fire:

"SELECT `date_a`,`date_b` FROM `occupied_room_dates`
WHERE `id`='".ROOM_ID."' AND 
(
    (`date_a`<='".DT_A."' AND `date_b`>='".DT_A."')
    OR (`date_a`<='".DT_B."' AND `date_b`>='".DT_B."')
    OR (`date_a`>='".DT_A."' AND `date_b`<='".DT_B."')
    OR (`date_a`<='".DT_A."' AND `date_b`>='".DT_B."')

) LIMIT 1"

But trying this on a real table containing a decent amount of rows, it is not working as expected. And I am having trouble with visualizing all the conditions that must be met, as well as with representing those conditions in a single MySQL query.

The purpose behind this is to check if a room is occupied in a date interval. Could you please help me with the query?

Upvotes: 0

Views: 119

Answers (1)

javan.rajpopat
javan.rajpopat

Reputation: 147

Explanation:

Your code pretty much seems right. But since you only have data from one table, you don't need Where ID = "Room_ID" as such. I didn't understand what exactly you wanted to output out of the 3 columns so I have done Select *, if you only want the RoomID, use SELECT id FROM occupied_room_dates instead.

Code:

SELECT * FROM occupied_room_dates
WHERE (date_a<="2019-02-03" AND date_b>="2019-02-03")
    OR (date_a<="2019-05-03" AND date_b>="2019-05-03")
    OR (date_a>="2019-02-03" AND date_b<="2019-05-03")
    OR (date_a<="2019-02-03" AND date_b>="2019-05-03")

Upvotes: 1

Related Questions