MySQL check if date range overlaps other date ranges?

Question

I am working on a car rental system using Codeigniter where a vehicle can have multiple unavailable date ranges. I have created a separate table for the unavailable date ranges, now I want the user to enter a date range and select all those cars that are not overlapping the unavailable date ranges of those cars. Following is my code.

    $d1 = date('Y-m-d', strtotime($pickup));
    $d2 = date('Y-m-d', strtotime($dropoff));
//   $this->db->where($d1." NOT BETWEEN date_ranges.start_date AND date_ranges.end_date AND " . $d2.' NOT BETWEEN date_ranges.start_date AND date_ranges.end_date');
    $this->db->where("'$d1' NOT BETWEEN date_ranges.start_date AND date_ranges.end_date AND '$d2' NOT BETWEEN date_ranges.start_date AND date_ranges.end_date OR ('$d1' < date_ranges.start_date AND '$d2' > date_ranges.end_date) ");
//   $this->db->where(" '$d1' <= date_ranges.end_date AND date_ranges.start_date <= '$d2'");

    $query = ->join('date_ranges', 'vehicles.id = date_ranges.vehicle_id', 'left')
    ->group_by('vehicles.id')
    ->get('vehicles');

Raw Query

 SELECT `vehicles`.`id`, `vehicles`.`year-`, `vehicles`.`model`, 
 `vehicles`.`nightly_rate`, `vehicles`.`class`, 
 `vehicle_pictures`.`picture`, 
 `vehicles`.`people` FROM `vehicles` LEFT JOIN `vehicle_pictures` ON 
  `vehicles`.`id` = `vehicle_pictures`.`vehicle_id` LEFT JOIN `date_ranges` 
  ON 
  `vehicles`.`id` = `date_ranges`.`vehicle_id` 
   WHERE ( `country` LIKE '%%' 
    ESCAPE 
    '!' OR `state` LIKE '%%' ESCAPE '!' OR `city` LIKE '%%' ESCAPE '!' OR 
    `street` 
    LIKE '%%' ESCAPE '!' OR `zip` LIKE '%%' ESCAPE '!' )
   AND '2017-05-23' 
   NOT 
   BETWEEN `date_ranges`.`start_date` AND date_ranges.end_date AND '2017-
   05-24' 
   NOT 
   BETWEEN `date_ranges`.`start_date` AND date_ranges.end_date OR ('2017-
   05-23' < 
   `date_ranges`.`start_date` AND '2017-05-24' > date_ranges.end_date) AND 
   `vehicles`.`people` > '1' GROUP BY `vehicles`.`id`;

Possible duplicate of [Check overlap of date ranges in MySQL](https://stackoverflow.com/questions/2545947/check-overlap-of-date-ranges-in-mysql) — JazZ, May 24 '17 at 09:15

silkfire · Accepted Answer · 2017-05-24T09:51:33.760

0

The basic algorithm for a date range that does NOT overlap is:

NOT(unavailable_date_range_START <= desired_date_range_END) AND NOT(unavailable_date_range_END >= desired_date_range_START)

So translated to your query, it should be something like:

 SELECT `vehicles`.`id`, `vehicles`.`year`, `vehicles`.`model`, `vehicles`.`nightly_rate`,
        `vehicles`.`class`, `vehicle_pictures`.`picture`, `vehicles`.`people` 

 FROM `vehicles`

 LEFT JOIN `vehicle_pictures` ON (`vehicles`.`id` = `vehicle_pictures`.`vehicle_id`)

 WHERE (   `country` LIKE '%%' ESCAPE '!'
        OR `state`   LIKE '%%' ESCAPE '!'
        OR `city`    LIKE '%%' ESCAPE '!'
        OR `street`  LIKE '%%' ESCAPE '!'
        OR `zip`     LIKE '%%' ESCAPE '!')
 AND `vehicles`.`id` NOT IN(SELECT DISTINCT `date_ranges`.`vehicle_id`

                            FROM `date_ranges`

                            WHERE `date_ranges`.`start_date` <= '$d2' AND `date_ranges`.`end_date` >= '$d1')

edited May 24 '17 at 09:51

answered May 24 '17 at 09:24

silkfire

24,585
15
82
105

Its only working if there is a single unavailable range in the table while in my case there can be multiple unavailable date ranges for each vehicle. – BTree May 24 '17 at 09:34
What do you get if there are multiple unavailable ranges? – silkfire May 24 '17 at 09:36
It selects the cars even the range overlaps if there are multiple unavailable ranges for the same vehicle!!! – BTree May 24 '17 at 09:41
If you invert the query and make it into a subquery, it should work – silkfire May 24 '17 at 09:43
can you give an example, as I dont know too much about sql. Thanks – BTree May 24 '17 at 09:44
@BTree Did it work out for you? If yes, please accept this answer. – silkfire May 24 '17 at 15:15
Thank you so much for the support and help, I have not tried the last solution yet, once I implement it and it works then i will accept it. :-) – BTree May 25 '17 at 09:50
1

Thank you @silkfire for you good idea of sub query and finally that worked for me. – BTree May 25 '17 at 13:59

score 0 · Answer 2 · answered May 25 '17 at 14:03

And finally the following query worked for me.

SELECT `vehilces`.`id`, `vehilces`.`year`, `vehilces`.`model`, 
`vehilces`.`nightly_rate`, `vehilces`.`class`, `vehilce_pictures`.`picture`, 
`vehilces`.`people`, `date_ranges`.`start_date`, `date_ranges`.`end_date` FROM 
`vehilces` LEFT JOIN `date_ranges` ON `vehilces`.`id` = 
`date_ranges`.`vehilce_id` LEFT JOIN `vehilce_pictures` ON `vehilces`.`id` = 
`vehilce_pictures`.`vehilce_id` WHERE ( `country` LIKE '%%' ESCAPE '!' OR 
`state` LIKE '%%' ESCAPE '!' OR `city` LIKE '%%' ESCAPE '!' OR `street` LIKE 
'%%' ESCAPE '!' OR `zip` LIKE '%%' ESCAPE '!' ) AND vehilces.id NOT IN(SELECT 
DISTINCT vehilce_id from date_ranges WHERE ('$d1' <= end_date)  and  ('$d2' >= 
start_date) or ('$d1' <= end_date)  and  (start_date <= '$d2')) AND 
`vehilces`.`people` > '1' GROUP BY `vehilces`.`id`

MySQL check if date range overlaps other date ranges?

2 Answers2