How to check if hour is on the next day php Laravel-CodePudding

I'm trying to get data from database, and if the created is today but the work_hour_end is on the next day, it will skip/ignore the data.

The data is like this:

id  |  work_hour_start  |  work_hour_end
1   |  11:00:00         |  15:00:00
2   |  21:00:00         |  03:00:00  // work hour end on the next day, end of the day is 23:59

I've trying like code bellow, but still not working. Because there's no date in the work_hour_end.

 $attendance_in = Attendance::where('employee_id', $id)
                ->whereDate('work_hour_end', Carbon::today())
                ->whereDate('created', Carbon::today())
                ->first();

CodePudding user response：

The whereTime method may be used to compare a column's value against a specific time:

$attendance_in = Attendance::where('employee_id', $id)
               ->whereTime('work_hour_end', '<', '23:59')
               ->whereDate('created', Carbon::today())
               ->first();

CodePudding user response：

As we can assume work_end_time will always be greater than work_start_time if the event is on the same day. If work_end_time is smaller or equal to work_start_time we can assume the event will be in the next day. So, to determine this we will have to compare work_end_time and work_start_time column of same db row to get exact result.

$attendance_in = Attendance::where('employee_id', $id)
                ->whereColumn('work_hour_start','>=','work_hour_end')
                ->whereDate('created', Carbon::today())
                ->first();