check if date ranges (d-m) overlap with a given date range (d-m)

hello guys,

i need some help to figure out a way to make this works:

i have a prices table with the following columns:
id
room_id
price
from_date (just day-month)
to_date (just day-month) i wanna give the user the ability to add a new price to specific room in a given date range, but before adding the price i need to find a way to check if the given date range overlaps with any date range for the specified room. i couldn't find a way to do this :(

here is an example: assume we have the following record in the prices table id room_id price from_date to_date 1 2 200 05-07 16-07

and the user wants to add the following price: room_id: 2 price: 400 from_date: 08-07 to_date: 12-07

this range overlaps with an already existing range in the database, what query should i write to check if a given range overlaps with already existing ranges in the database??

Thanks guys.

jbloomstrom

4 years ago

Level 50

Two date ranges ($start1-$end1 and $start2-$end2) overlap if these conditions are both true:

$start2 < $end1 && $start1 < $end2

A query in Laravel would look something like this:

// find overlaps
$query->where('from_date','<',$selected_to_date)
    ->where('to_date','<',$selected_from_date);

Also, I would strongly suggest storing the dates in the standard format: YYYY-mm-dd, it will help avoid a lot of confusion (e.g. a date range spanning December-January).

1 like

Please or to participate in this conversation.