how to get data from 19:00:00 pm to 07:00:00 am-CodePudding

$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

CodePudding user response：

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).

CodePudding user response：

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');