Ad

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_fromdate_to.Both columns type is datetime.When I pass date like 2019-03-20 09:00:002019-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.

Ad

Answer

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();
Ad
source: stackoverflow.com
Ad