MySQL: Validate for date range within rate range (laravel optional)

Question

Hey guys (and girlies)

Can someone point out to validate / search for a date range within a date range? I am using Laravel PHP but raw SQL will be perfect as well.

I want to validate if a date range exists, within a date range. Basically I want a solid validation to avoid overlapping date ranges.

Example (and the concept I have at the moment) (laravel syntax)

->where('start_date','>=',$myrange_start) ->where('start_date','<=',$myrange_end)

Which will validate for for a start date in the database between the date range

and

->where('start_end','>=',$myrange_start) ->where('start_end','<=',$myrange_end)

Which will validate for for an end date in the database between the date range

And which has also kind of worked for me is

->whereRaw('? between start_date and end_date', [$today])

however, what I want to achieve is to say something like

->whereRaw('? between start_date and end_date', [$myrange_start, $myrange_end])

so see if there are date ranges which

a) start: on start, between start and end, on end b) start: on end c) end: on start, between start and en, on end d) start BEFORE start date and ends AFTER ends date e) start AFTER start date and ends BEFORE end date

Is this possible?

Your first code is fine, if a date range is inside another one then both `start_date` and `end_date` of that range will be between `start_date` and `end_date` of the other one — Nick, Jan 04 '19 at 06:23
@you can use whereBetween. You can check link https://laravel.com/docs/5.7/queries — Bhavin Thummar, Jan 04 '19 at 06:27

score 3 · Answer 1 · answered Jan 04 '19 at 06:28

3

This is the overlapping range problem, and to quote this canonical SO answer, the WHERE clause should be something like:

WHERE new_start < existing_end
    AND new_end > existing_start;

Translating this to Laravel code:

->where('start_date', '<', $myrange_end)
->where('start_end', '>', $myrange_start)

This would return true if there is an overlap between the two ranges. If instead you want to find out if there isn't an overlap, then we can try:

->where('start_date', '>=', $myrange_end)
->orWhere('start_end', '<=', $myrange_start)

answered Jan 04 '19 at 06:28

Tim Biegeleisen

502,043
27
286
360

Thanx for your help! So I think this should then do the job. (trying to post formatted styling in the comment dosnt seem to work `where(function ($q) use ($start, $end) { $q->where('start_date', '>=', $start) ->where('start_date', '<=', $end); }) ->orWhere(function ($q) use ($start, $end) { $q->where('end_date', '>=', $start) ->where('end_date', '<=', $end); }) ->orWhere(function ($q) use ($start, $end) { $q->where('start_date', '<', $start) ->where('end_date', '>', $end); });` – Derick Van Wyk Jan 04 '19 at 06:35

score 1 · Answer 2 · answered Jan 04 '19 at 09:04

1

Laravel official docs laravel docs whereBetween

ModelName::whereBetween('date_field', [ $start_Date, $end_date ])->get();

answered Jan 04 '19 at 09:04

Milan Tarami

70
1
8

score 0 · Answer 3 · answered Jan 04 '19 at 06:40

Okay, think this should then work. you guys can comment

        where(function ($q) use ($start, $end) {
            $q->where('start_date', '>=', $start)
            ->where('start_date', '<=', $end);
        })
        ->orWhere(function ($q) use ($start, $end) {
            $q->where('end_date', '>=', $start)
                ->where('end_date', '<=', $end);
        })
        ->orWhere(function ($q) use ($start, $end) {
            $q->where('start_date', '<', $start)
                ->where('end_date', '>', $end);
        });

MySQL: Validate for date range within rate range (laravel optional)

3 Answers3