预订日历到达和离开日期

Question

This may seem a typical question, but I can't find an answer to this problem.

I have a booking table:

book_id  |   arrive_date |   depart_date
1                 2015-07-20     2015-07-22 
2                 2015-07-22     2015-07-23  
3                 2015-07-19     2015-07-20  

You will see that I have demonstrated that a depart date can be an arrive date and vice versa

Before a date is added I check that the date range won't conflict with dates in the table, it is important to allow a depart date to be an arrive date as the person will be leaving, to allow this when I check the dates in the table I ADD a date to the arrive_date and SUBTRACT a day from the depart_date using this SQL:

Select * From booking Where booking.unit_id = 58 
  And ( DATE_ADD(booking.from_date, INTERVAL 1 DAY) 
  BETWEEN '2015-07-23' AND '2015-07-24' 
   OR 
  DATE_SUB(booking.to_date, INTERVAL 1 DAY) 
   BETWEEN '2015-07-23' AND '2015-07-24' 
      OR '2015-07-23' 
   BETWEEN DATE_ADD(booking.from_date, INTERVAL 1 DAY) 
   AND DATE_SUB(booking.to_date, INTERVAL 1 DAY)) Limit 1  

When I added the first date, no problem...same with the second and third, this worked because the first date range I entered a day in between the (20th and 22nd). Then I try and add '2015-07-23' AND '2015-07-24', which is checked using the above SQL, which is obviously returning a result. I need a way that will allow these dates to be accepted as the 23rd is a depart_date.

Any help would be appreciated

I should also mention that I also have a table that holds blocked dates "booking_prev", therefore a similar query will also be used to check block dates to those in the booking table before they are entered.

Answer 1

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(book_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,arrive_date DATE NOT NULL
,depart_date DATE NOT NULL
);

INSERT INTO my_table VALUES
(1,'2015-07-20','2015-07-22'),
(2,'2015-07-22','2015-07-23'),
(3,'2015-07-19','2015-07-20');

SELECT * FROM my_table;
+---------+-------------+-------------+
| book_id | arrive_date | depart_date |
+---------+-------------+-------------+
|       1 | 2015-07-20  | 2015-07-22  |
|       2 | 2015-07-22  | 2015-07-23  |
|       3 | 2015-07-19  | 2015-07-20  |
+---------+-------------+-------------+

So, the SELECTs might look like this...

SELECT '2015-07-22','2015-07-24' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y
    ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-22'
 WHERE y.book_id IS NULL 
 LIMIT 1;
Empty set (0.00 sec)

SELECT '2015-07-23','2015-07-24' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y
    ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-23'
 WHERE y.book_id IS NULL 
 LIMIT 1;
+------------+------------+
| 2015-07-23 | 2015-07-24 |
+------------+------------+
| 2015-07-23 | 2015-07-24 |
+------------+------------+

...but you don't need to check them first. I've deliberately written the SELECTs in such a way that the check can happen as part of the INSERT...

INSERT INTO my_table (arrive_date,depart_date)
SELECT '2015-07-23','2015-07-24' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y
    ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-23'
 WHERE y.book_id IS NULL 
 LIMIT 1;

SELECT * FROM my_table;
+---------+-------------+-------------+
| book_id | arrive_date | depart_date |
+---------+-------------+-------------+
|       1 | 2015-07-20  | 2015-07-22  |
|       2 | 2015-07-22  | 2015-07-23  |
|       3 | 2015-07-19  | 2015-07-20  |
|       4 | 2015-07-23  | 2015-07-24  |
+---------+-------------+-------------+

In practice, and depending on the user experience you want to provide, you may want to run the SELECTs first, so that the user can immediately see which dates aren't available, and then run the INSERTs when it comes to making the booking - to make sure that no one grabbed those dates while the user was mid-way through booking.