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:

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:

The checkIn maybe in the middle of another holiday and the checkOut may not.
The checkOut maybe in the middle of another holiday and the checkIn may not.
They maybe identical dates to another holiday.
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?

you don't mention the business logic behind this - what is a "holiday room", does it make a difference? what defines a holiday in relation to a date (weekend? date period?)? clarify what you WANT to do, and what are the actual restraints so that we can help. — Nick Andriopoulos
– Nick Andriopoulos, Commented Sep 9, 2013 at 14:12
Date period... The admin of that room can make holidays in that room as long as they want. They choose an checkIn Date and a checkOut Date. That's all the information I need to check — Ashley Williams
– Ashley Williams, Commented Sep 9, 2013 at 14:18
For future code: You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a column name that is a reserved word, and using column names that are reserved words is a terrible idea, so that's two bad habits you can avoid at once. — Andy Lester
– Andy Lester, Commented Sep 9, 2013 at 15:11

NDM · Accepted Answer · 2013-09-09 14:50:50Z

6

        |________|      // 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 :)

edited Sep 9, 2013 at 14:50

answered Sep 9, 2013 at 14:20

NDM

6,8483 gold badges42 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ashley Williams Over a year ago

The code you have provided makes the most sense to me, however your all your =< should be <= etc. However, using what you have provided(with the fix I just mentioned) I can no longer book any dates, even if there is a gap inbetween

NDM Over a year ago

fixed the operators. also note that if this query returns results, you can not book during the period. not the other way round.

Ashley Williams Over a year ago

I understand that, if you look at this image: postimg.org/image/4dh2c72at can see what I mean, I have asked for a week that is not already booked in my database and it is still returned

Marc B · Accepted Answer · 2013-09-09 14:31:26Z

4

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.

answered Sep 9, 2013 at 14:31

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

Joni · Accepted Answer · 2013-09-09 14:33:45Z

2

To arrive at the condition of when there is an overlapping range, it's easier to think about where there is no overlap and then reverse the condition. Two ranges do not overlap if one is completely before or after the other.

In SQL this translates to checkout date being before the given checkin date, or the checkin date being after the given checkout date:

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

You can simplify this to:

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

answered Sep 9, 2013 at 14:33

Joni

112k14 gold badges151 silver badges201 bronze badges

Collectives™ on Stack Overflow

Using MYSQL to select rows between dates?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related