在开始日期列和结束日期列之间搜索开始日期和结束日期 MySQL,Laravel

Question

这是我的事件表结构。

+----+----------------------+---------------------+

| id |      start_date      |      end_date       |

+----+----------------------+---------------------+

|  1 | 2020-07-01 09:00:00  | 2021-07-11 15:00:00 |

+----+----------------------+---------------------+

我想获取给定时间段内的所有记录，例如我想获取 2020-07-27 - 2020-09-07 之间的所有记录。这是我到目前为止所尝试过的

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

它不获取任何记录。你们能检查一下并指导我遗漏或做错了什么吗？

Answer 1

尝试如下查询。$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]])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->orwhereBetween('start_date',array($start_date,$end_date))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;->orWhereBetween('end_date',array($start_date,$end_date))->get();

Answer 2

首先，您不需要格式化日期以在“之间”使用它......只需按原样使用它......第二：要获取一天中的第一个小时或最后一个小时，您可以使用：startOfDay()、endOfDay() 方法：$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();

Answer 3

试试这个科，$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-&nbsp;>toDateTimeString()])->orWhereBetween('end_date', [$start_date->toDateTimeString() ,&nbsp;$end_date->toDateTimeString()])->get();