Laravel 7 DB Query only where date range not in date range

Question

I have something like this:

$from = min($start_date, $end_date);
$till = max($start_date, $end_date);

DB::table('booked')
     ->where('start', '<=', $from)
     ->where('end', '>=', $till)
     ->get();

This is a nice solution form stackoverflow.

It seems works, but I need the opposite.

In my DB I have this data: start: 2020-09-27 10:00:00 end: 2020-09-27 12:00:00 I have to query that rows where for example:

$start_date(2020-09-27 9:00:00) and $end_date(2020-09-27 11:00:00) where both not in start: and end: Hopefully my question is clear enough.

your end_date 2020-09-27 11:00:00 is between the given range and your telling in opposite way — Dark Knight
– Dark Knight, Commented Sep 25, 2020 at 21:37
Yes this is the point, if it is between the given range then I don't want it in the result. Only if not in the given range. — Robert
– Robert, Commented Sep 25, 2020 at 21:47
so it would be like, if any parameter start or end date is between given date range, you don't want to include — Dark Knight
– Dark Knight, Commented Sep 25, 2020 at 21:53
Yes exactly, just the given date range is stored in the db, and the other range is variable. So I would like to check that the variable is not in the stored range. — Robert
– Robert, Commented Sep 25, 2020 at 22:01
Just to be clear. Let's say data in db are start - 10, end = 12 and now if input range is start = 8, end = 9 => yes, start = 9, end = 11 => no, start = 11, end = 11:30 => no, start = 11, end = 13 => no, start = 13, end = 14 = yes. If that so, will give you a query — Dark Knight
– Dark Knight, Commented Sep 25, 2020 at 22:04

ahappydev · Accepted Answer · 2020-09-25 22:12:53Z

1

If I am getting you right this is what you need

$from = min($start_date, $end_date);
$till = max($start_date, $end_date);

DB::table('booked')
     ->where('start', '>=', $till)
     ->orWhere('end', '<=', $from)
     ->get();

answered Sep 25, 2020 at 22:12

ahappydev

1135 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ahappydev Over a year ago

It assumes you have correct(logically) data in database.

Robert Over a year ago

Hmm, this is great! Thank you very much, I just work on this on the last 2 hours and I can't understand why not working properly! (Probably I need some rest) Cheers!

Robert Over a year ago

This is really works well, but I have a problem. If I have more date in the database then it not working anymore, any idea?

ahappydev Over a year ago

What you mean by "more date"? What is not working? Please, describe.

Collectives™ on Stack Overflow

Laravel 7 DB Query only where date range not in date range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related