Using MYSQL to select rows between dates?

Question

Im having a little problem with some MYSQL I have.

I need to be able to check if a period of time has already been booked in a holiday room.

My database uses DATETIME Fields and is set out like this:

Database image link, as my rep is low

I have a query like:

SELECT * FROM `caravanavail1` 
WHERE (`checkIn` BETWEEN '2014-01-07 14:00:00' AND '2014-01-08 10:00:00') 
OR (`checkOut` BETWEEN '2014-01-07 14:00:00' AND '2014-01-08 10:00:00')

I will always pass in two dates, however sometimes the dates may clash in many ways:

1. The checkIn maybe in the middle of another holiday and the checkOut may not.
2. The checkOut maybe in the middle of another holiday and the checkIn may not.
3. They maybe identical dates to another holiday.
4. The checkIn and checkOut dates I provide may BOTH be inside another booking.

I have been scratching my head for a few days with this, can anyone help me fix the SQL code above(I would prefer a single query, rather than two) to return what I need?

Answer 1

        |________|      // date to book
    |______|            // condition 1
              |______|  // condition 2
          |____|        // condition 3 
    |________________|  // condition 4

If any of these 4 conditions results in a row, this means the booking can not be completed.

$in =  '2014-01-07 14:00:00';
$out = '2014-01-08 10:00:00';

SELECT * 
FROM `caravanavail1` 
WHERE 
  (`checkIn` <= '$in' AND `checkOut` <= '$out') // condition 1
  OR 
  (`checkIn` >= '$in' AND `checkOut` >= '$out')  // condition 2
  OR 
  (`checkIn` >= '$in' AND `checkOut` <= '$out') // condition 3
  OR 
  (`checkIn` <= '$in' AND `checkOut` >= '$out') // condition 4

As Marc B also notes, this can be further simplified: all checkin times of the conditions are before the checkout time you want to book AND all check out times of the conditions are after the checkin time you want to book

comming down to this:

SELECT * 
FROM `caravanavail1` 
WHERE 
  (`checkIn` < '$out' AND `checkOut` > '$in')

sometimes it helps to visualize :)

Answer 2

Consider this diagram. Where x and y are the checkin/checkout time in question, and p and q are the checkin/checkout times in your database. There's various ways these two date ranges can overlap:

     x   y
p  q               // complete non-overlap, no conflict
   p  q            // partial overlap, conflict
      p q          // partial overlap, conflict
       p  q        // partial overlap, conflict
           p   q   // complete non overlap, no conflict

If you plot out the logic, you'll find that

 if (q >= x) && (p <= y) {
       ... there is a conflict
 }

coverse your bases.