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?
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();
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();
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();