skip to Main Content
$star = $request->input('start_date'). ' ' . '19:00:00';
$end = $request->input('end_date'). ' ' . '07:00:00';
$employee_qty_in_shift2 = PatrolGateSurveillanceTransaction::where('type', 1)
        ->where('client_location_id', $request->client_location_id)
        ->where('created_at', '>=', $star)
        ->where('created_at', '<=', $end)
        ->sum('employee_qty'); dd($employee_qty_in_shift2);

The above code cannot filter data from 19:00:00 to 07:00:00

Expected result:

created_at 
2022-10-12 19:00:00 
2022-10-13 20:00:00 
2022-10-12 21:00:00 
2022-10-12 22:00:00 
2022-10-12 23:00:00 
2022-10-12 23:59:59 
2022-10-13 01:00:00 
2022-10-13 02:00:00 
2022-10-13 03:00:00 
2022-10-13 04:00:00 
2022-10-13 05:00:00 
2022-10-13 06:00:00 
2022-10-13 07:00:00

2

Answers


  1. The above code cannot filter data from 19:00:00 to 07:00:00

    Of course. The is no value which is above 19:00:00 and below 07:00:00 at the same time.

    Possible solution 1: Check that the time value is above 19:00:00 OR that it is below 07:00:00. I.e. use OrWhere, not Between (mixing AND and OR – do not forget about operators precedence).

    Possible solution 2: substract 19 hours from the value then check that the timepart of the result is between 00:00:00 and 12:00:00.

    In any case you must extract the timepart from complete datetime value – i.e. you must check not created_at but TIME(created_at).

    Login or Signup to reply.
  2. You want to use whereBetween, not two where clauses. Using where implies AND, not OR, and you can’t have a condition where the value will be above 19:00:00 AND below 07:00:00 (for the same date, anyways).

    $star = $request->input('start_date'). ' ' . '19:00:00';
    $end = $request->input('end_date'). ' ' . '07:00:00';
    $employee_qty_in_shift2 = PatrolGateSurveillanceTransaction::where('type', 1)
            ->whereBetween('created_at',[$star,$end])
            ->where('client_location_id', $request->client_location_id)
            ->sum('employee_qty');
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search