php 如何使用 Laravel 和 Eloquent 在两个日期之间进行查询？

How to query between two dates using Laravel and Eloquent?

提问by wobsoriano

I'm trying to create a report page that shows reports from a specific date to a specific date. Here's my current code:

我正在尝试创建一个报告页面，显示从特定日期到特定日期的报告。这是我当前的代码：

$now = date('Y-m-d');
$reservations = Reservation::where('reservation_from', $now)->get();

What this does in plain SQL is select * from table where reservation_from = $now.

这在普通 SQL 中的作用是select * from table where reservation_from = $now.

I have this query here but I don't know how to convert it to eloquent query.

我在这里有这个查询，但我不知道如何将其转换为 eloquent 查询。

SELECT * FROM table WHERE reservation_from BETWEEN '$from' AND '$to

How can I convert the code above to eloquent query? Thank you in advance.

如何将上面的代码转换为 eloquent 查询？先感谢您。

回答by Peter Kota

The whereBetweenmethod verifies that a column's value is between two values.

该whereBetween方法验证列的值是否介于两个值之间。

$from = date('2018-01-01');
$to = date('2018-05-02');

Reservation::whereBetween('reservation_from', [$from, $to])->get();

---

---

In some cases you need to add date range dynamically. Based on @Anovative's comment you can do this:

在某些情况下，您需要动态添加日期范围。根据@Anovative的评论，您可以这样做：

Reservation::all()->filter(function($item) {
  if (Carbon::now->between($item->from, $item->to) {
    return $item;
  }
});

---

---

If you would like to add more condition then you can use orWhereBetween. If you would like to exclude a date interval then you can use whereNotBetween.

如果您想添加更多条件，则可以使用orWhereBetween. 如果您想排除日期间隔，则可以使用whereNotBetween.

Reservation::whereBetween('reservation_from', [$from1, $to1])
  ->orWhereBetween('reservation_to', [$from2, $to2])
  ->whereNotBetween('reservation_to', [$from3, $to3])
  ->get();

---

---

Other useful where clauses: whereIn, whereNotIn, whereNull, whereNotNull, whereDate, whereMonth, whereDay, whereYear, whereTime, whereColumn, whereExists, whereRaw.

其他有用的 where 子句：whereIn, whereNotIn, whereNull, whereNotNull, whereDate, whereMonth, whereDay, whereYear, whereTime, whereColumn, whereExists, whereRaw.

Laravel docs about Where Clauses.

Laravel 关于 Where 子句的文档。

回答by tomloprod

Another option if your field is datetimeinstead of date(although it works for both cases):

如果您的字段datetime不是date（尽管它适用于两种情况），则另一种选择：

$fromDate = "2016-10-01";
$toDate   = "2016-10-31";

$reservations = Reservation::whereRaw(
  "(reservation_from >= ? AND reservation_from <= ?)", 
  [$fromDate." 00:00:00", $toDate." 23:59:59"]
)->get();

回答by P??

The following should work:

以下应该工作：

$now = date('Y-m-d');
$reservations = Reservation::where('reservation_from', '>=', $now)
                           ->where('reservation_from', '<=', $to)
                           ->get();

回答by John

If you want to check if current date exist in between two dates in db: =>here the query will get the application list if employe's application from and to date is exist in todays date.

如果您想检查当前日期是否存在于 db 中的两个日期之间：=> 如果今天的日期中存在员工的申请，则查询将获取申请列表。

$list=  (new LeaveApplication())
            ->whereDate('from','<=', $today)
            ->whereDate('to','>=', $today)
            ->get();

回答by ArtisanBay

Try this:

尝试这个：

Since you are fetching based on a single column value you can simplify your query likewise:

由于您是基于单个列值获取数据，因此您可以同样简化查询：

$reservations = Reservation::whereBetween('reservation_from', array($from, $to))->get();

Retrieve based on condition: laravel docs

根据条件检索：laravel docs

Hope this helped.

希望这有帮助。

回答by MIGUEL LOPEZ ARIZA

If you need to have in when a datetime field should be like this.

如果你需要在 datetime 字段应该是这样的。

return $this->getModel()->whereBetween('created_at', [$dateStart." 00:00:00",$dateEnd." 23:59:59"])->get();

回答by Manojkiran.A

And I have created the model scope

我已经创建了模型范围

More about scopes:

有关范围的更多信息：

• https://laravel.com/docs/5.8/eloquent#query-scopes

• https://medium.com/@janaksan_/using-scope-with-laravel-7c80dd6a2c3d

• https://laravel.com/docs/5.8/eloquent#query-scopes

• https://medium.com/@janaksan_/using-scope-with-laravel-7c80dd6a2c3d

Code:

代码：

   /**
     * Scope a query to only include the last n days records
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeWhereDateBetween($query,$fieldName,$fromDate,$todate)
    {
        return $query->whereDate($fieldName,'>=',$fromDate)->whereDate($fieldName,'<=',$todate);
    }

And in the controller, add the Carbon Library to top

并在控制器中，将碳库添加到顶部

use Carbon\Carbon;

OR

或者

use Illuminate\Support\Carbon;

To get the last 10 daysrecord from now

从现在开始获取最近 10 天的记录

 $lastTenDaysRecord = ModelName::whereDateBetween('created_at',(new Carbon)->subDays(10)->toDateString(),(new Carbon)->now()->toDateString() )->get();

To get the last 30 daysrecord from now

从现在开始获取最近 30 天的记录

 $lastTenDaysRecord = ModelName::whereDateBetween('created_at',(new Carbon)->subDays(30)->toDateString(),(new Carbon)->now()->toDateString() )->get();

回答by Excellent Lawrence

I know this might be an old question but I just found myself in a situation where I had to implement this feature in a Laravel 5.7 app. Below is what worked from me.

我知道这可能是一个老问题，但我发现自己不得不在 Laravel 5.7 应用程序中实现此功能。以下是我的工作。

 $articles = Articles::where("created_at",">", Carbon::now()->subMonths(3))->get();

You will also need to use Carbon

您还需要使用碳

use Carbon\Carbon;