MySQL query records for continuous date range

Question

here's my example table (room reservation system):

id   available     room_id
----------------------------
1    2014-02-05    4
2    2014-02-06    4
3    2014-02-07    4
4    2014-02-09    4
5    2014-02-10    4

i want to query if room with id 4 is available between 2014-02-05 and 2014-02-10.

i know i can query by using the BETWEEN operator, but the problem is that i need to consider continuous date ranges, so it should return zero records as the record for 2014-02-08 is missing. any ideas?

thanks

Wouldn't it be more logical to record dates (ranges) for when the room is booked!?!? — Strawberry, Feb 05 '14 at 17:04
select all available dates, throw them in array and then compare in php? — bart2puck, Feb 05 '14 at 17:05
I recommend trying `DATEDIFF` to get the numeric number of days between dates, and then using a subquery to count the number of available days for that room. If `DATEDIFF() = COUNT(*)` then the room is available. See http://stackoverflow.com/a/7857565/482115 and http://lists.mysql.com/mysql/196414 ... however I agree with @Strawberry, this booking system is a little wacky. Definitely makes more sense to store the booked/unavailable dates. — degenerate, Feb 05 '14 at 17:26

score 1 · Answer 1 · answered Feb 05 '14 at 17:35

Here is an idea. Count the number of rows that match and then compare these to the number of days in the period:

select room_id
from example
where available between date('2014-02-05') and date('2014-02-10')
group by room_id
having count(*) = datediff(date('2014-02-05'), date('2014-02-10')) + 1;

MySQL query records for continuous date range

1 Answers1