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

laravel wherein
laravel db::query
laravel eloquent
speed up mysql queries large tables
whererow
mysql query optimization techniques
mysql millions of rows performance
php number between range

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?

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)

Database: Query Builder - Laravel, It can be used to perform most database operations in your application and works always validate the column names against a white-list of allowed columns. For example, let's work with the entire users table in chunks of 100 records at a time: This method accepts an optional array of bindings as its second argument​: before:date. The field under validation must be a value preceding the given date. The dates will be passed into the PHP strtotime function. In addition, like the after rule, the name of another field under validation may be supplied as the value of date. before_or_equal:date. The field under validation must be a value preceding or equal to the

Laravel official docs laravel docs whereBetween

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

PHP range() Function, Specifies the lowest value of the array. high, Required. Specifies the highest value of the array. step, Optional. Specifies the increment used in the range. Default  This module collects common validation routines to check suspect values against MySQL column types. For example, you can check to make sure your integer values are within range, your strings aren't too big, and that your dates and times look vaguely ISO-ish.

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);
        });

4. Query Performance Optimization, Beware of placing too much emphasis on query execution time. The access types range from a full table scan to index scans, range scans, unique index If MySQL does find a match in the query cache, it must check privileges before returning the cached query. The optimizer's job is to find the best option. Once Laravel Easyrec is installed, you need to register the service provider. Open up config/app.php and add the following to the providers key. 'Antoineaugusti\LaravelEasyrec\LaravelEasyrecServiceProvider'

Working with Dates and Times in PHP and MySQL, When working in any programming language, dealing with dates and time is often a look at MySQL dates and show you how to make them play nicely with PHP. It takes an optional Unix timestamp argument, but defaults to the value of with database queries that use date ranges customized by a user. Our open-source projects used internally at Rackbeat. - Rackbeat

Top 10 MySQL Mistakes Made By PHP Developers, Validate every string using server-side PHP — don't rely on JavaScript. Watch out also for SQL queries within PHP loops. Normally, it's more MySQL offers a range of numeric, string, and time data types. MySQL may be the most widely used database for PHP developers, but it's not the only option. Migrations are typically paired with Laravel's schema builder to build your application's database schema. If you have ever had to tell a teammate to manually add a column to their local database schema, you've faced the problem that database migrations solve.

Making Sense of the Metadata: Clustering 4,000 Stack Overflow tags , You can check out more about working with Stack Overflow data and BigQuery here and here. It also reduces the time for training the model in BigQuery from 24 minutes to 3. centroid 9: php, laravel, laravel-5, mysql, javascript —– clickhouse, text-editor, kettle, angularjs-material, date-range, rpy2,  Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks.

Comments
  • 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
  • @you can use whereBetween. You can check link laravel.com/docs/5.7/queries
  • 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); });