$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
Of course. The is no value which is above
19:00:00
and below07:00:00
at the same time.Possible solution 1: Check that the time value is above
19:00:00
OR that it is below07:00:00
. I.e. useOrWhere
, notBetween
(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
and12:00:00
.In any case you must extract the timepart from complete datetime value – i.e. you must check not
created_at
butTIME(created_at)
.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).