Checking for date range conflicts in MySQL

Question

I am writing a hotel booking system. after lots of studying (including stack overflow) i wrote this sql to find out free rooms:

SELECT
*
FROM room
WHERE
    room_id NOT IN (
        SELECT room_id
        FROM bookings
        WHERE
                 checkin <= '$check_in'
            AND checkout >= '$check_out'
    )

but the problem is its not considering the time checking is 12:00:00 and checkout is 11:59:00

also its not giving the right queries like within the date range its not working like if I book from 15-18 a single room who's number is 501. if I again run a query 17-19 this rooms seems free but in reality it should be occupied.

can anyone suggest a very good and effective sql which will get the accurate date so that no clash will happen booking system because the system will be implemented in real so mistakes will cause many issues.

thanks in advance

Answer 1

Your original logic was very close, you just need to swap the '$check_in' and '$check_out' values. I.e.:

SELECT *
FROM room
WHERE room_id NOT IN
(
    SELECT room_id
    FROM bookings
    WHERE checkin <= '$check_out' AND checkout >= '$check_in'
)

Brian Driscoll's answer focusses on the scenarios that constitute booking conflicts, as so:

---------------|-----Booked-----|---------------
       |----A1----|
                             |----A2----|
                    |--A3--|
            |----------A4----------|

Case A2 & A3: checkin <= '$check_in' AND checkout >= '$check_in'
Case A1 & A3: checkin <= '$check_out' AND checkout >= '$check_out'
Case A4:      checkin >= '$check_in' AND checkout <= '$check_out'

However the senarios that constitute no conflict are much simpler. There are only two:

---------------|-----Booked-----|---------------
  |----B1----|                             
                                  |----B2----|

Case B1: checkin > '$check_out'
Case B2: checkout < '$check_in'

So the situation where there is no conflict between a booking and a potential booking can be expressed with this SQL:

checkin > '$check_out' OR checkout < '$check_in'

To check for conflicts instead, we just need to negate this. So, using DeMorgans Law, the negation is:

checkin <= '$check_out' AND checkout >= '$check_in'

...which arrives at the solution given above.

Answer 2

The problem you're having is that your query is not sufficiently robust. When you break down the problem, what you have is this:

If the range defined by $check_in and $check_out overlaps the range defined by checkin and checkout in any way, then the room is booked. Otherwise, it is free.

This means that:

• If $check_in >= checkin and $check_in <= checkout, the room is BOOKED
• OR If $check_out >= checkin and $check_out <= checkout, the room is BOOKED
• OR If $check_in <= checkin and $check_out >= checkout, the room is BOOKED

So, you need to represent both of these scenarios in your subquery in order to get the information you're looking for.

Also, you will hopefully be using datetime for your comparisons and not just time, otherwise you will have side effects.

EDIT: SQL Query

(Keep in mind that there is more than one way to skin a cat, so to speak. I'm just providing an example that keeps with what you already have as much as possible. Once again, I'm also assuming that checkin, checkout, $check_in, and $check_out will all resolve to datetime types)

SELECT *
FROM room
WHERE room_id NOT IN
(SELECT room_id 
 FROM bookings
 WHERE
   (checkin <= '$check_in' AND checkout >= '$check_in') OR
   (checkin <= '$check_out' AND checkout >= '$check_out') OR
   (checkin >= '$check_in' AND checkout <= '$check_out'))

Answer 3

I think this might get you started in the right direction...

SELECT R.*
FROM room AS R
     LEFT OUTER JOIN bookings AS B USING (room_id)
WHERE B.room_id IS NULL
      OR (B.checkout < '$check_in'
          AND B.checkin > '$check_out')