Laravel 的 whereBetween 不包括日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44158949/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel's whereBetween not including date from
提问by Sylar
Note: Code should work for postgres and mysql.
注意：代码应该适用于 postgres 和 mysql。
I want to query a table by date_fromand date_to. Let's say there is a date range with format: ('d-m-Y')
我想通过date_from和查询表date_to。假设有一个日期范围的格式：('d-m-Y')
Foo:
福：
[
{'id': 1, "created_at": "2017-05-20 11:18:00"},
{'id': 2, "created_at": "2017-05-24 11:38:36"}
]
Code:
代码：
$format = 'd-m-Y';
$df = $request->input('date_from');
$dt = $request->input('date_to');
$date_from = Carbon::createFromFormat($format, $df);
$date_to = Carbon::createFromFormat($format, $dt);
Foo::whereBetween('created_at', [$date_from, $date_to])->get()->toArray();
When I query date_from = '20-05-2017'and date_to = '20-05-2017', I see an empty array. I expect to see 20-05-2017Foo's regardless. If I date_from = '19-05-2017'and date_to = '21-05-2017', I see 20-05-2017's array.
当我查询date_from = '20-05-2017'and 时date_to = '20-05-2017'，我看到一个空数组。20-05-2017无论如何，我希望看到Foo's。如果 Idate_from = '19-05-2017'和date_to = '21-05-2017'，我会看到20-05-2017的数组。
What's wrong with whereBetween?
怎么了whereBetween？
回答by Alex Harris
It is working as expected. The problem you are encountering is primarily that you are asking for the time between the exact same timestamp. While you are only specifying a specific day and not a time, a time is being included. In this case it is most likely exactly midnight. So '20-05-2017'becomes '20-05-2017 00:00:00'. In this case there are no times except that exact time which will satisfy your whereBetweencall.
它按预期工作。您遇到的问题主要是您要求完全相同的时间戳之间的时间。虽然您只指定了特定的日期而不是时间，但时间也被包括在内。在这种情况下，很可能正好是午夜。于是'20-05-2017'变成'20-05-2017 00:00:00'。在这种情况下，除了满足您whereBetween呼叫的确切时间之外，没有其他时间。
If you want to get records of a specific date the easiest solution for you would be to use:
如果您想获取特定日期的记录，最简单的解决方案是使用：
$query->whereRaw('date(created_at) = ?', [date('Y-m-d')]);
If you are looking to query between two differentdates, your current method will work.
如果您想在两个不同的日期之间进行查询，您当前的方法将起作用。
If you want the start time to be the beginning of the day and the end time to be the end of the day you can use Carbon's modifiersfor this startOfDayand endOfDay:
如果您希望开始时间是一天的开始，而结束时间是一天的结束，您可以为此使用 Carbon 的修饰符startOfDay和endOfDay：
$date_from = Carbon::parse($request->input('date_from'))->startOfDay();
$date_to = Carbon::parse($request->input('date_to'))->endOfDay();
$result = Foo::whereDate('created_at', '>=', $date_from)
->whereDate('created_at', '<=', $date_to)
->get()
->toArray();
回答by Autista_z
It is working correctly.
它工作正常。
Problem is that you are passing in whereBetween date only Y-m-dformat and laravel is comparing this format with Y-m-d H:i:sformat of created_at column.
问题是您传入 whereBetween 仅日期Y-m-d格式，而 Laravel 正在将此格式与Y-m-d H:i:screated_at 列的格式进行比较。
When you are passing only Y-m-d, it will be transformed to Y-m-d 00:00:00. So your created_at 2017-05-20 11:18:00is not between 20-05-2017 00:00:00and 20-05-2017 00:00:00.
当您仅通过时Y-m-d，它将转换为Y-m-d 00:00:00. 所以你的 created_at2017-05-20 11:18:00不在20-05-2017 00:00:00和之间20-05-2017 00:00:00。
回答by Sandeesh
createFromFormatcreates the date from the given input and replaces the missing time with your current time. So use parseinstead, you don't even need to specify the form. Secondly you're using whereBetweenwhich checks the complete timestamp and not just the date. You're first row 2017-05-20 11:18:00is outside the range of dates, therefore it won't return the value. You need to use whereDateto check for items based on date alone while ignoring the time.
createFromFormat从给定的输入创建日期，并用您的当前时间替换缺少的时间。所以parse改用，你甚至不需要指定表单。其次，您正在使用whereBetweenwhich 检查完整的时间戳，而不仅仅是日期。您的第一行2017-05-20 11:18:00超出了日期范围，因此它不会返回该值。您需要使用whereDate仅根据日期检查项目而忽略时间。
$date_from = Carbon::parse($request->input('date_from'));
$date_to = Carbon::parse($request->input('date_to'));
$result = Foo::whereDate('created_at', '>=', $date_from)
->whereDate('created_at', '<=', $date_to)
->get()
->toArray();
回答by Scotty G
I know this is old but you can now use this
我知道这是旧的，但你现在可以使用 this
->whereBetween('created_at', [$date_from, $date_to])
in placeof
in place的
->whereDate('created_at', '>=', $date_from)
->whereDate('created_at', '<=', $date_to)
