英文:
How to check if date is in the future for separate date and time columns?
问题
我有两个不同的日期列:date
本身以YYYY-mm-dd
格式,和一个time
列,数据类型为time(7)
,例如11:15:10.0000000
。
我该如何检查未来的行?
我可以获取日期本身的部分:
MyModel::where('date', '>=', Carbon::today())->get()
但是,当我尝试添加时间时,它不起作用:
MyModel::where('date', '>=', Carbon::today())->where('time', '>', Carbon::now()->format('H:i'))->get()
因为它们是分开的,即使日期在未来,时间也是分开的,所以可能存在时间不匹配的情况。所以我需要以某种方式将日期和相关的时间一起考虑在未来,而不是分开处理。
英文:
I have 2 separate columns for the date: the date
itself in YYYY-mm-dd
format, and a time
column in time(7)
datatype, for example 11:15:10.0000000
How can I check for rows that are in the future?
I can get the first part, for the day itself:
MyModel::where('date', '>=', Carbon::today())->get()
But when I try adding the time it doesn't work:
MyModel::where('date', '>=', Carbon::today())->where('time', '>', Carbon::now()->format('H:i'))->get()
because they are separate and now even though the date is in the future, the time is separate so there may be a situation where the time doesn't match. So I somehow need to have both the date and the time related to it in the future, not separately
答案1
得分: 1
尝试将两列合并为一个条件。
$now = Carbon::now();
MyModel::whereRaw("CONCAT(`date`, ' ', `time`) >= ?", [$now->toDateTimeString()])->get();
对于SQL Server,请尝试以下操作:
MyModel::whereRaw("CONVERT(datetime, date + ' ' + CONVERT(varchar, time, 121)) >= ?", [$now->toDateTimeString()])->get();
查询转换可能需要更新,更多信息请参考文档。
英文:
Try to combine both the columns in a single condition.
$now = Carbon::now();
MyModel::whereRaw("CONCAT(`date`, ' ', `time`) >= ?", [$now->toDateTimeString()])->get();
For SQL Server try following
MyModel::whereRaw("CONVERT(datetime, date + ' ' + CONVERT(varchar, time, 121)) >= ?", [$now->toDateTimeString()])->get();
Query conversion may need to update, for more information try documentation
答案2
得分: 1
比较数据类型在这里很重要。所以,你可以使用orWhere
来组合两个不同的条件。为此,你需要传递一个回调函数来将条件分组在一个原始格式的where
条件中的_AND_中。
MyModel::where('date', '>', Carbon::today())
->orWhere(function($query){
$query->where('date', '=', Carbon::today())
->where('time', '>', Carbon::now()->format('H:i'));
})->get();
英文:
Comparison via datatype is important here. So, you can use orWhere
to combine 2 different conditions. For this, you will need to pass a callback to group conditions inside one for the AND in where conditions in raw format.
<?php
MyModel::where('date', '>', Carbon::today())
->orWhere(function($query){
$query->where('date', '=', Carbon::today())
->where('time', '>', Carbon::now()->format('H:i'));
})->get();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论