CODEWITHSUNDEEP
mysqllaraveldatetimedate-rangelaravel-query-builder
Gayal Seneviratne
Gayal Seneviratne

Reputation: 115

Get database search results between two date time period

In my database, there is a table called vehicle_schedule, and all the vehicle schedules are stored there. In that table, there is two columns name as date_from date_to.Both columns type is datetime.When I pass date like 2019-03-20 09:00:00 2019-03-25 12:00:00 I need to get all the vehicle schedules between that two DateTime range.

So far I have tried this,

$vehicleSchedule=DB::table('vehicle_schedule')
->select('vehicle_schedule.*')
->whereRaw("date_from >=? AND date_to <=?",array($date_from,$date_to))
->get();
return response()->json(["vehicleSchedule"=>$vehicleSchedule,"message"=>"Vehicle Schedule got successfully"]);

I expect all the vehicle schedules are in that given range. But it only got vehicle schedules between date values only. When I check with the time constraint it does not working properly.

Upvotes: 0

Views: 138

Answers (3)

smartrahat
smartrahat

Reputation: 5609

I highly recommend to use PHP Carbon to work with any datetime or timestamp field.

To convert the datetime field to Carbon use this in model.

protected $dates = ['date_from','date_to'];

Then, convert the input time to Carbon inside controller

$date_from = Carbon::parse('2019-03-20 09:00:00');
$date_to = Carbon::parse('2019-03-25 12:00:00');

In this way the query will more shorter, cleaner and efficient.

$vehicleSchedule = DB::table('vehicle_schedule')
    ->where('date_from','>=',$date_from)
    ->where('date_to','<=',$date_to)
    ->get();

Upvotes: 1

party-ring
party-ring

Reputation: 1871

You can use Laravel whereBetween for this:

$vehicleSchedule=DB::table('vehicle_schedule')
    ->select('vehicle_schedule.*')
    ->whereBetween('date_from', [$startDate, $endDate])
    ->whereBetween('date_to', [$startDate, $endDate])
    ->get();

This will only select records where the date_from and date_to are between the two dates.

Related SO post. Documentation.

Upvotes: 2

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

Try this.

$start_date = date('Y-m-d H:i:s',strtotime('2019-03-20 09:00:00'));
$end_date = date('Y-m-d H:i:s',strtotime('2019-03-25 12:00:00'));

$vehicleSchedule=DB::table('vehicle_schedule')
->select('vehicle_schedule.*')
->where([['date_from','<=',$start_date],['date_to','>=',$end_date]])->orwhereBetween('date_from',array($start_date,$end_date))->orWhereBetween('date_to',array($start_date,$end_date))
->get();

Upvotes: 1

Related Questions