CODEWITHSUNDEEP
phplaravellaravel-5eloquent
shaNnex
shaNnex

Reputation: 1083

Checking dates if between range of dates in Laravel 5

I have a booked table with start and end column as datestamps;

I know that this

$start = "2015-07-12 01:00:00";
$end = "2015-07-12 02:00:00";

$users = DB::table('booked')
                    ->whereBetween('start', [$start, $end])->get();

checks for any dates in start column that falls within $start and $end.

What I want is actually the other way.

Am having difficulty checking for $start and $end date variables occurrences in start and end columns in book table.

Upvotes: 4

Views: 8805

Answers (4)

Saad Mateen
Saad Mateen

Reputation: 21

I resolved my issue with this code, in my case, I have multiple blocked dates, in which we can't allow user to register a new booking, so we have to check the following 12 Wheres:

$start_date = $request->start_date; 
$end_date = $request->end_date; 
DB::table('booked')->where(function ($query) use ($start_date, $end_date) {
    $query->where(function ($q) use ($start_date, $end_date) {
        $q->where('start_date', '>=', $start_date)
            ->where('start_date', '=<', $start_date);
    })->orWhere(function ($q) use ($start_date, $end_date) {
        $q->where('end_date', '>=', $start_date)
            ->where('end_date', '<=', $start_date);
    })->orWhere(function ($q) use ($start_date, $end_date) {
        $q->where('start_date', '>=', $end_date)
            ->where('start_date', '<=', $end_date);
    })->orWhere(function ($q) use ($start_date, $end_date) {
        $q->where('end_date', '>=', $end_date)
            ->where('end_date', '<=', $end_date);
    })->orWhere(function ($q) use ($start_date, $end_date) {
        $q->where('start_date', '<=', $start_date)
            ->where('end_date', '>=', $start_date);
    })->orWhere(function ($q) use ($start_date, $end_date) {
        $q->where('start_date', '<=', $end_date)
            ->where('end_date', '>=', $end_date);
    });
})->count();

Upvotes: 0

Er Parwinder Hirkewal
Er Parwinder Hirkewal

Reputation: 306

Please Try under given code this will check is requested dates time are already book or not, if any time slot is booked then it will return 1 otherwise 0 :

$start = $request->start_date; 
$end = $request->end_date; 
DB::table('booked')->where(function ($query) use ($start, $end) {

    $query->where(function ($q) use ($start, $end) {
        $q->where('start', '>=', $start)
           ->where('start', '<', $end);

    })->orWhere(function ($q) use ($start, $end) {
        $q->where('start', '<=', $start)
           ->where('end', '>', $end);

    })->orWhere(function ($q) use ($start, $end) {
        $q->where('end', '>', $start)
           ->where('end', '<=', $end);

    })->orWhere(function ($q) use ($start, $end) {
        $q->where('start', '>=', $start)
           ->where('end', '<=', $end);
    });

})->count();

Upvotes: 6

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81167

// make sure $from is before $till, because they are probably provided as input
$from = min($start, $end);
$till = max($start, $end);

// then simply
DB::table('booked')
     ->where('start', '<=', $from)
     ->where('end', '>=', $till)
     ->get();

It will return rows matching $from-$till period contained in start-end period:

    start                     end
      |------------------------|
         |----------------|
       $from            $till

Upvotes: 7

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40909

The following query will give you all records for which both start and end are between $start and $end:

$users = DB::table('booked')
  ->where('start', '>=', $start)
  ->where('start', '<=', $end)
  ->where('end', '>=', $start)
  ->where('end', '<=', $end);

Upvotes: -1

Related Questions