Search Start & End dates between start and end date columns MySQL,Laravel

user_12167963 0
Viewed 1977

This is my events Table structure.

+----+----------------------+---------------------+
| id |      start_date      |      end_date       |
+----+----------------------+---------------------+
|  1 | 2020-07-01 09:00:00  | 2021-07-11 15:00:00 |
+----+----------------------+---------------------+

I want to fetch all records in a given time period e.g I want to fetch all records that are between 2020-07-27 - 2020-09-07. This is what i have tried so far

$start_date = Carbon::parse("$req->startDate 00:00:00")->format('Y-m-d H:i:s');
$end_date= Carbon::parse("$req->endDate 23:59:59")->format('Y-m-d H:i:s');


Events::whereBetween('start_date', [$start_date , $end_date])->orWhereBetween('end_date', [$start_date , $end_date])->get();

It doesn't fetches any records. Can you guys please check it and guide me what i am missing or doing wrong?

eloquent laravel mysql php
3 Answers

Try the query as below.

$start_date = Carbon::parse("$req->startDate 00:00:00")->format('Y-m-d H:i:s');
$end_date = Carbon::parse("$req->endDate 23:59:59")->format('Y-m-d H:i:s');

$events = Events::where([['start_date','<=',$start_date],['end_date','>=',$end_date]])
              ->orwhereBetween('start_date',array($start_date,$end_date))
             ->orWhereBetween('end_date',array($start_date,$end_date))->get();
user_99780780

first of all, you don't need to format the date to use it in 'between'.... just use it as it is ....

second: for getting the first hour in day or the last you can use: startOfDay(),endOfDay() methods:

$start_date = Carbon::parse($req->startDate )->startOfDay();
$end_date= Carbon::parse($req->endDate)->endOfDay();
Events::whereBetween('start_date', [$start_date , $end_date])->orWhereBetween('end_date', [$start_date , $end_date])->get();
user_105735600

Try this coe,

$start_date = Carbon::parse("$req->startDate 00:00:00")->format('Y-m-d H:i:s');
$end_date= Carbon::parse("$req->endDate 23:59:59")->format('Y-m-d H:i:s');


Events::whereBetween('start_date', [$start_date->toDateTimeString() , $end_date- 
>toDateTimeString()])->orWhereBetween('end_date', [$start_date->toDateTimeString() , 
$end_date->toDateTimeString()])->get();
user_73029630
Related