如何正确检索两个日期之间的 Laravel 模型

假设你有一个博客，里面有文章（或者一个广告牌，上面有列表；或者一个旅行者的旅行记录……你应该明白我的意思），你想检索两个日期之间的所有文章。听起来是不是很熟悉？那么我们究竟该怎么做呢？

坦诚的态度

这看起来确实是一个很简单的问题，答案也很容易（但完全错误）：只需像这样使用BETWEEN（或者在 Laravel 中）：whereBetween

$startDate = '2021-06-01';
$endDate = '2021-06-30';

$posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();

我们都这样做过（至少我知道我这样做过），目的是检索六月份创建的所有帖子。问题在于，我们的created_at列通常是日期时间类型，所以它不仅仅是一个简单的日期，还包含时间。这意味着实际上任何在6月30日创建的帖子都无法被检索到，因为它们的创建日期总是大于2021-06-30（SQL会将其视为“2021-06-30 00:00:00”）。

或者我们可能正在使用 Carbon，并且有类似这样的情况：

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01');
$endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30');

$posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();

实际上情况更糟，因为它变得完全不可预测。Carbon 实例代表一个瞬间，它也有一个时间，但如果您不指定时间，它将默认为脚本运行时的当前时间。因此，如果您在早上 9 点运行此脚本，而帖子是在 30 号早上 8 点创建的，您将可以检索到它……但是，如果您在早上 7 点运行完全相同的脚本，您将无法再检索到该帖子，因为 $endDate 实际上将是 '2021-06-30 07:00:00'。

我们可以使用 $endDate->toDateString() 去掉时间，但这样就会出现上述情况。

更好的碳利用方式

一种解决方案是确保我们在查询中指定一个时间，并且该时间对于我们的开始日期是当天的开始时间（00:00:00），对于我们的结束日期是当天的结束时间（23:59:59.999999）。

幸运的是，Carbon 提供了可以实现这一点的工具startOfDay()和endOfDay()方法：

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01')->startOfDay();
$endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30')->endOfDay();

$posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();

这样就好多了，我们可以相当肯定，无论创建时间或当前时间如何，所有在 1 号或 30 号创建的内容都将被检索出来。

这是一个可靠的解决方案，你当然可以使用它，但是我们实际上只关心日期，却还要添加时间，这感觉有点像是在耍小聪明，所以我们来看看另一个解决方案。

使用 MySQL 的另一种方法

我们还可以使用 `.` 明确告诉 MySQL 我们只关心日期DATE()。我们想要的查询是这样的：

SELECT * FROM posts
  WHERE DATE(created_at) BETWEEN '2021-06-01' AND '2021-06-30'

这样我们就可以比较日期本身，而不是与 Datetime 类型进行比较。我们需要使用 DB:raw() 函数才能在 Eloquent 中实现这一点，代码如下所示：

$startDate = '2021-06-01';
$endDate = '2021-06-30';

Post::whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])->get();

理想情况下，我们应该确保日期$startDate格式$endDate正确，但即使我们传递一个完整的 Carbon 对象（会自动转换为字符串），它似乎也能正常工作，因为 MySQL 会忽略时间部分。

所以这是另一种方法，但我也不喜欢使用 DB::raw()，而且它的速度可能更慢（最后一段会详细说明）。那么，让我们来看看利用 Eloquent 来处理这个问题的最终解决方案。

另一种用 Eloquent 的方式

Eloquent 提供了一种非常有用的whereDate()方法，可以做到两件事。

1. 构建一个 SQL 查询，使用DATE()SQL 函数将列的内容格式化为Ymd。
2. 在进行比较之前，请将 Carbon 或 Datetime 对象正确地转换为Ymd格式。

利用这一点，我们可以自信地传递 Carbon 实例，并知道其中任何时间点都将被丢弃，而我们实际上将搜索两个日期之间的数据：

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01');
$endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30');

$posts = Post::query()
    ->whereDate('created_at', '>=', $startDate)
    ->whereDate('created_at', '<=', $endDate)
    ->get();

这将生成以下 SQL 查询：

SELECT * from "posts"
WHERE DATE("created_at") >= '2021-06-01'
AND DATE("created_at") <= '2021-06-30';

而且它运行完美。唯一的缺点是不能使用 `between`，所以写起来稍微长一些，但如果要在多个地方使用它，我们可以轻松地编写一个作用域（甚至可以将其泛化，以便它可以作为 Trait 导入到每个需要它的模型中？），类似这样：

public function scopeCreatedBetweenDates($query, array $dates)
{
    return $query->whereDate('created_at', '>=', $dates[0])
        ->whereDate('created_at', '<=', $dates[1])
}

改用它：

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01');
$endDate   = Carbon::createFromFormat('Y-m-d', '2021-06-30');

$posts = Post::createdBetweenDates([$startDate, $endDate])->get();

我觉得这看起来相当不错！可惜的是，这可能不是最快的解决方案……

性能如何？

注：这并非原文的一部分，LinkedIn 上有人问我“性能如何”……这引发了一系列我始料未及的新问题（Oliver 也在评论中指出了这一点）。

如果没有在 created_at 列上建立索引，以上所有方法的性能都差不多。如果你只需要按日期筛选几十个或几百个模型，这可能没问题；但是，如果你要处理比这多得多的模型，你就需要创建一个索引，然后……事情就会变得很复杂。

如果在该列上创建一个简单的索引，那么created_at使用 Carbon 的方法会快得多->startOfDay()，因为它可以利用索引。->endOfDay()->between()

遗憾的是，任何涉及该DATE()函数的操作，无论是使用DB::raw()Laravel 的WhereDate()函数还是其他函数，都无法使用该索引，因为它包含时间戳。

理论上，解决此问题的方法是在MySQL 中创建函数索引DATE(created_at)。但需要注意两点：

1. 函数索引仅在 MySQL 8.0.13 及更高版本中可用。
2. 出于我无法理解的原因，使用预处理语句时，函数索引不会被使用。因此，为了使其正常工作，您还必须使用 PDO 的模拟预处理语句，但这并非推荐的做法。

总而言之，我的最终建议是继续使用 Carbon 来获取每天的开始和结束时间。我们仍然可以为此使用作用域，使其更易于使用（我们还会使其接受 Carbon 实例或字符串）：

public function scopeCreatedBetweenDates($query, array $dates)
{
    $start = ($dates[0] instanceof Carbon) ? $dates[0] : Carbon::parse($dates[0]);
    $end   = ($dates[1] instanceof Carbon) ? $dates[1] : Carbon::parse($dates[1]);

    return $query->whereBetween('created_at', [
        $start->startOfDay(),
        $end->endOfDay()
    ]);
}

我们可以这样使用它：

$posts = Post::createdBetweenDates(['2021-06-01', '2021-06-30'])->get();

结论

处理时间很困难。我发现，在处理日期和时间时，即使看起来很简单，也值得多花一分钟时间思考一下，它真的那么简单吗？你是否遗漏了什么？（我们甚至还没谈到时区、夏令时和闰秒……）

还有什么我没提到的吗？有什么问题？有什么约会恐怖故事想分享？欢迎在评论区留言！