skip to Main Content

How can I check if there is already a working hour in a database for a specific user in a given time slot. So when there is a working hour which is overlapping with the given dates and times, the function should return true. I tried this, but this doesn’t work.

  function checkExistingWorkingHours($user_id, $start_date, $start_time, $end_date, $end_time)
{
    // dd($user_id . " " . $start_date . " " . $start_time . " " . $end_date . " " . $end_time);

    $startDateTime = Carbon::parse($start_date . ' ' . $start_time);
    $endDateTime = Carbon::parse($end_date . ' ' . $end_time);

    $overlapExists = WorkingHour::where('user_id', $user_id)
    ->where(function ($query) use ($startDateTime, $endDateTime) {
        $query->where(function ($query) use ($startDateTime, $endDateTime) {
            // Check for overlap where the start_datetime is between existing start_datetime and end_datetime
            $query->where(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '=', $startDateTime->format('Y-m-d'))
                    ->where('start_time', '<', $endDateTime->format('H:i'))
                    ->where('end_date', '=', $startDateTime->format('Y-m-d'))
                    ->where('end_time', '>', $startDateTime->format('H:i'));
            })->orWhere(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '<', $startDateTime->format('Y-m-d'))
                    ->where('end_date', '=', $startDateTime->format('Y-m-d'))
                    ->where('end_time', '>', $startDateTime->format('H:i'));
            });
        })->orWhere(function ($query) use ($startDateTime, $endDateTime) {
            // Check for overlap where the end_datetime is between existing start_datetime and end_datetime
            $query->where(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '=', $endDateTime->format('Y-m-d'))
                    ->where('end_date', '>=', $endDateTime->format('Y-m-d'))
                    ->where('start_time', '<', $endDateTime->format('H:i'));
            })->orWhere(function ($query) use ($startDateTime, $endDateTime) {
                $query->where('start_date', '<=', $startDateTime->format('Y-m-d'))
                    ->where('end_date', '>=', $endDateTime->format('Y-m-d'));
            });
        });
    })->exists();

    return $overlapExists;
}

When I test the function with the following data, it says, that there is an overlapping, even there isn’t an overlapping.

checkExistingWorkingHours(1, 2023-06-01, 00:15, 2023-06-01, 03:00);

And in the database is a value 2023-06-01 from 03:30 to 11pm.

2

Answers


  1. Chosen as BEST ANSWER

    Answer according to the comment of geertjanknapen:

    $period = CarbonPeriod::create("$start_date $start_time", "$end_date $end_time");
    $start = Carbon::create("$to_check->start_date $to_check->start_time");
    $end = Carbon::create("$to_check->end_date $to_check->end_time");
    
    
    if ($period->overlaps($start, $end)) {
        return redirect()->back()->withInput()->with('error',"Time is overlapping!");
    }
    

  2. Try to use this

    $start = Carbon::parse($start_date . ' ' . $start_time)->stratOfDay();
    $end = Carbon::parse($end_date . ' ' . $end_time)->endOfDay();
    $overlapExists = WorkingHour::where('user_id', $user_id)->whereBetween('your column',[$start , $end])->exists();
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search