Check time range between two columns laravel

Question

The following is migration. I want to implement a condition. But I don't know how to check time range between start_time and end_time.

  Schema::create('settings', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');
            $table->time('start_time');
            $table->time('end_time');
            $table->foreign('user_id')->references('id')->on('users');
            $table->timestamps();
        });

following is what I'm trying.

     $date =  new DateTime("Asia/Karachi");
     $date = $date->format('Y-m-d H:i:s');
     $date = date('H:i:s',strtotime($date));

        $users_with_crypto_only_sms_alert = 
      User::whereNotNull('device_key')
        ->where('subscription_package', 'LIKE', '%Morpheus%')
         ->where('role', '=', 'user')
         ->where('sms_alerts', '=', 1)
         ->where('is_email', '=', 0)
         ->where('is_blocked', '=', 0)
         ->where('mobile_no', '!=', '')
          ->whereHas('setting',function($q) use($date){
                        $q->whereTime('start_time','>=',$date);
                        $q->whereTime('end_time','<=',$date);
                    })->pluck('device_key')->all();
                    dd($users_with_crypto_only_sms_alert);

please be more specific. What did u try? explain more about what you want, in what you want it and what the expected result should be. — MESP, Mar 01 '22 at 12:29
I'm saving setting for quiet time notifications aganist authenticated user — mqamar, Mar 01 '22 at 12:39
I just want to check if time exists between start_time column and end_time column — mqamar, Mar 01 '22 at 12:41
check this, if it help: https://stackoverflow.com/questions/2920335/how-to-calculate-time-difference-in-php#:~:text=php%20function%20timeDiff(%24firstTime%2C,Usage%20%3A%20echo%20timeDiff(%222002 — Ibrahim Hammed, Mar 01 '22 at 12:41

score 1 · Answer 1 · answered Mar 01 '22 at 12:46

1

[Opinionated] You should not use time as the field type of start_time and end_time. You can use whether timestamp or datetime to be able calculate the time differences.

If you want to use datetime, you can use this answer https://stackoverflow.com/a/39665726/3673225

If you want to use timestamp, you can use this answer https://stackoverflow.com/a/2920518/3673225

answered Mar 01 '22 at 12:46

zaidysf

492
2
14

I don't need to calculate time difference I simply need to check the range between start_time and end_time – mqamar Mar 01 '22 at 12:50
i understand your issue, laravel will read your time as a string, because of that you are facing this issue, you should change the field type of both start_time and end_time to make your above query works. – zaidysf Mar 01 '22 at 12:56

Check time range between two columns laravel

1 Answers1