Laravel Eloquent 比较日期时间字段中的日期
我想通过一个表达式从一个表中获取所有行:
I want to get all the rows from a table through an expression:
table.date <= 2014-07-10
但如果该列包含日期时间，我们就说:
But if the column contains a datetime let's say:
2014-07-10 12:00:00
但如果我这样做:
where('date', '<=', $date)
它不会得到该行.
我猜这是因为 $date = 2014-07-10 这使得 MySQL 假设它是 2014-07-10 00:00:00.
I guess this is because $date = 2014-07-10 which makes MySQL assume that it is 2014-07-10 00:00:00.
在常规 MySQL 中我会这样做
In regular MySQL I would just do
where DATE(date) <= $date
使用 Laravel 的 Eloquent 的等价物是什么?
What would be the equivalent using Laravel's Eloquent?
推荐答案
Laravel 4+ 为你提供了这些方法:whereDay(), whereMonth(), whereYear() (#3946) 和 whereDate() (#6879).
Laravel 4+ offers you these methods: whereDay(), whereMonth(), whereYear() (#3946) and whereDate() (#6879).
他们为您执行 SQL DATE() 工作，并管理 SQLite 的差异.
They do the SQL DATE() work for you, and manage the differences of SQLite.
你的结果可以这样实现:
Your result can be achieved as so:
->whereDate('date', '<=', '2014-07-10')
更多例子请看#3946的第一条消息和这个Laravel Daily 文章.
For more examples, see first message of #3946 and this Laravel Daily article.
更新: 虽然上述方法很方便，但正如 Arth 所说，它在大型数据集上效率低下，因为必须对每条记录应用 DATE() SQL 函数，因此丢弃可能的索引.
Update: Though the above method is convenient, as noted by Arth it is inefficient on large datasets, because the DATE() SQL function has to be applied on each record, thus discarding the possible index.
以下是一些进行比较的方法(但请阅读下面的注释):
Here are some ways to make the comparison (but please read notes below):
->where('date', '<=', '2014-07-10 23:59:59')
->where('date', '<', '2014-07-11')
// '2014-07-11'
$dayAfter = (new DateTime('2014-07-10'))->modify('+1 day')->format('Y-m-d');
->where('date', '<', $dayAfter)
注意事项:
- 23:59:59 可以(暂时)因为 1 秒的精度，但请看这篇文章:23:59:59 不是一天的结束.不，真的！
- 请记住零日期"情况(0000-00-00 00:00:00").尽管应该避免这些零日期"，但它们是许多问题的根源.如果需要，最好使该字段可以为空.
相关文章