Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
How do you query between two ranges of time without including the two ranges themselves?
I have a working code of querying time using whereBetween but I'm having an issue because I only want to get the records between those two ranges of time, not including themselves.
I have these rows on my schedules table:
+----------+------------------+----------------+-------------+
| id | start_time | end_time | user_id |
+----------+------------------+----------------+-------------+
| 1 | 06:45:00 | 07:45:00 | 1 |
| 2 | 10:30:00 | 11:30:00 | 1 |
+----------+------------------+----------------+-------------+
Steps I'm doing:
schedules table.start_time of 07:45:00 and end_time of 08:45:00.The code I use to check if there is a row that in between the $startTime and $endTime.
$userId = 1;
$startTime = Carbon::parse('07:45')->format('H:i:s');
$endTime = Carbon::parse('08:45')->format('H:i:s');
$checkScheduleIfExists = Schedule::where('user_id', $userId)
->where(function ($query) use ($startTime, $endTime) {
$query->whereBetween('start_time', [
$startTime, $endTime
])
->orWhereBetween('end_time', [
$startTime, $endTime
]);
})
->first();
dd($checkScheduleIfExists);
The problem with the code above is it finds the $startTime in the schedules table's end_time which is the 07:45:00 and it returns that row, same goes if it finds the $endTime in start_time.
What I expect is only to return a row (to know a record exists) if the $startTime (07:45:00) occupied lower than end_time that exists in my schedules table, for example, 7:40:00
mysql pure query
SELECT * FROM products WHERE created_at BETWEEN 2022-10-01 AND 2022-10-01;
in eloquent
$from = date('2022-01-01');
$to = date('2022-05-02');
Product::whereBetween('created_at', [$from, $to])->get();
more ... https://www.itsolutionstuff.com/post/how-to-get-data-between-two-dates-in-laravelexample.html
@johnDoe220 Thanks @johndoe220 , but I'm not talking about date my question is for time only.
The scenario is exactly the same. You can put the time instead of the date.
SELECT * FROM products WHERE TIME(created_at) BETWEEN 10:20:30 AND 13:10:22;
elouent
Product::whereTime('timestamp', '>=', \Carbon\Carbon::parse('06:00'))
->whereTime('timestamp', '<=', \Carbon\Carbon::parse('06:30'))->get()
@benjamin1509 mysql between is inclusive. Just replace it with the manual conditions
$checkScheduleIfExists = Schedule::where('user_id', $userId)
->where(function ($query) use ($startTime, $endTime) {
$query->whereTime('start_time', '>=', $startTime)
->whereTime('end_time', '<=', $endTime);
})
->first();
@SilenceBringer thank you, just a question, I don't want to check if the to be added range of time is equal to the existing one, so I should remove = right?
@benjamin1509 no, you need to keep it. if end_time with 10:00 exists and your $endTime contains 10:00 too - it must be excluded
For example, record with start time = 9:00' and end time = 10:00` exists
if you check for $startTime = 9:00 and $endTime = 10:00 without = - it will find nothing. But the time is busy
@SilenceBringer I see your point but in my case let's use row #1 in the table above.
row #1 in schedules table
06:45:00 - 07:45:00
to be added
07:45:00 - 08:45:00
In the current code included in my post, it will return row #1 from my schedules table, which is NOT the behavior I want.
The behavior I want is, only to return a row from the schedules table if the to-be-added time OVERLAPPED the existing ones.
for example:
row #1 in schedules table
06:45:00 - 07:45:00
to be added
07:40:00 - 08:45:00
You see here, 07:40:00 overlapped the 07:45:00 then this should return row #1 from the schedules table.
NOTE: checking for overlapped time can be any of start_time and end_time
@benjamin1509 oh, I see. this way
$checkScheduleIfExists = Schedule::where('user_id', $userId)
->where(function ($query) use ($startTime, $endTime) {
$query->where(function ($subQuery) {
$subQuery->whereTime('start_time', '>=', $startTime)
->whereTime('start_time', '<', $endTime);
})
->orWhere(function ($subQuery) {
$subQuery->whereTime('end_time', '<=', $endTime)
->whereTime('end_time', '>', $startTime);
});
})
->first();
@SilenceBringer wow you got it, I just need to update the code a little bit because it's complaining about undefined variables $startTime and $endTime, it's because of the missing use(). Thank you very much!
$userId = 1;
$startTime = Carbon::parse('07:40')->format('H:i:s');
$endTime = Carbon::parse('08:40')->format('H:i:s');
$checkScheduleIfExists = Schedule::where('user_id', $userId)
->where(function ($query) use ($startTime, $endTime) {
// ↓ this
$query->where(function ($subQuery) use ($startTime, $endTime) {
$subQuery->whereTime('start_time', '>=', $startTime)
->whereTime('start_time', '<', $endTime);
})
// ↓ this
->orWhere(function ($subQuery) use ($startTime, $endTime) {
$subQuery->whereTime('end_time', '<=', $endTime)
->whereTime('end_time', '>', $startTime);
});
})
->first();
i'm not clear about your requirements
do you mean that there should not be another record which overlaps with the proposed insert?
@Snapey Yes, you are right, providing another example based on the table rows above:
I don't want the new record to OVERLAP with any of the old ones. So, for example, start_time: 07:10 - end_time: 07:20 should also get row #1 returned because 07:20 is still inside 07:45.
@Snapey Here's a detailed explanation: https://laracasts.com/discuss/channels/eloquent/how-to-query-between-two-ranges-of-time?page=1&replyId=843174
@Snapey up
Please or to participate in this conversation.