Checking Between Date Range

@inspiredprynce

$isBooked = Booking::where('apartment_id', request('apartment_id'))
    ->whereDate('starting_at', '>=', request('starting_date'))
    ->whereDate('ending_at', '<=', request('ending_date'))
    ->get();

Not sufficient @automica, what if an existing booking starts before, and ends after, the requested booking; the apartment is still booked for the request dates. Several other scenarios are possible where the requested booking overlaps with an existing one.

@tykus indeed. I'm racking my brains for how I last did an overlap.

Try this:

// assuming your form fields are named
// starting_date, ending_date and room_id
$isBooked = DB::table('bookings')
    ->where('room_id', request('room_id'))
    ->where('starting_at', '<=', request('ending_date'))
    ->where('ending_at', '>=', request('starting_date'))
    ->exists();

return $isBooked ? 'yes' : 'no';

Check if you need the equal sign on the comparisons in general you can have a booking starting in the same day another booking ends, because the first guest leaves at morning and the second one enters afternoon.

If you can't have any overlap even on boundaries change <= to < and >= to > on the conditions above

more info here: https://stackoverflow.com/a/325964

@inspiredprynce mine is incorrect. @rodrigo.pedra got the answer.

although he got the first column wrong:

$isBooked = DB::table('bookings')
    ->where('apartment_id', request('apartment_id'))
    ->whereDate('starting_at', '<=', request('ending_date'))
    ->whereDate('ending_at', '>=', request('starting_date'))
    ->exists();

return $isBooked ? 'yes' : 'no';

What do you suggest sir?

Oh, let me mark his as correct then! Thanks alot for your contribution

Don't worry @automica , as long @inspiredprynce gets it working I don't mind not having the best reply marked upon.

But thanks for commenting out =)

@inspiredprynce use the last snippet from @automica , I used room_id instead of apartment_id.

This last snippet takes into account the general overlap case, read the stackoverflow link I posted for a more in-depth explanation of the range overlapping problem.

@rodrigo.pedra you'll love that I initially did what you did and doubted my query so reversed the < and >

/facepalm

You're welcome, have a nice day =)

@automica I forgot to factor the time factor in, please how do I include the time also in the query? Thanks guys!

@automica it helps to best of us!

I did this so many times before and had the same doubt, that is why I went to stackoverflow for confirmation.

@inspiredprynce what do you mean by "factor the time"?

You mean you want the number of days an existing reservation has?

@inspiredprynce if you store your starting_at and ending_at columns as dateTime, then you can make a date time to compare it with:

$startDateTime = new Carbon(request('starting_date') . ' ' . request('starting_time'));
$endDateTime = new Carbon(request('ending_date') . ' ' . request('ending_time'));

$isBooked = DB::table('bookings')
    ->where('apartment_id', request('apartment_id'))
    ->whereDate('starting_at', '<=', $endDateTime)
    ->whereDate('ending_at', '>=', $startDateTime)
    ->exists();

return $isBooked ? 'yes' : 'no';

in my experience, I don't think check in / checkout time matters as usually they are set by establishment and there is a gap for cleaning between checkout and check in time.

Or do you want or to compare both date and time?

If that is the case I think the same query will work as long as all your dates are formatted to include the time, something like this:

2020-10-19 13:12:00

Assuming a room has a booking dates of 20/10/2020 12:19:19 and ending date of 28/10/2020 12:19:19

Then someone searches for 19/10/2020 13:14 and 28/10/2020 11:15

How do I take note of the time there (11:15)

DateTime::__construct(): Failed to parse time string (19-10-2020 19-10-2020) at position 11 (1): Double date specification

I got thiis

@inspiredprynce you've copied a date in twice. not a date and a time.

If both date and time come within the same request field you can parse it this way:

$startDateTime = Carbon::createFromFormat('d/m/Y H:i|', request('starting_date'));
$endDateTime = Carbon::createFromFormat('d/m/Y H:i|', request('ending_date'));

$isBooked = DB::table('bookings')
    ->where('apartment_id', request('apartment_id'))
    ->whereDate('starting_at', '<=', $endDateTime)
    ->whereDate('ending_at', '>=', $startDateTime)
    ->exists();

return $isBooked ? 'yes' : 'no';

Note the | at the end of format will set the seconds to 0.

Also I am assuming both starting_at and ending_at columns in your table have a datetime data type.

request('arrival') => "2020-10-19 18:50"
request('departure') => "2020-10-31 15:50"

The separation symbol could not be found Unexpected data found. Unexpected data found. Unexpected data found. Trailing data

I got this

I assumed you were sending dates in a d/m/Y H:i format per your previous message. But it seems you are sending them in the Y-m-d H:i format.

use this instead:

$startDateTime = Carbon::createFromFormat('Y-m-d H:i|', substr(request('arrival'), 0, 16));
$endDateTime = Carbon::createFromFormat('Y-m-d H:i|', substr(request('departure'), 0, 16));

$isBooked = DB::table('bookings')
    ->where('apartment_id', request('apartment_id'))
    ->whereDate('starting_at', '<=', $endDateTime)
    ->whereDate('ending_at', '>=', $startDateTime)
    ->exists();

return $isBooked ? 'yes' : 'no';

The substr is to avoid trailing characters.

Also consider adding a date_format validation rule to your form validation.

Something like this:

'arrival' => 'required|date_format:Y-m-d H:i',
'departure' => 'required|date_format:Y-m-d H:i',

Either on a Form Request's rules method or on a controller method $this->validate(...) call.

Thanks alot for the validation tip! Thanks alot @automica and @tykus

Am grateful

You're welcome, have a nice day =)