如何检查日期是否在将日期和时间分开的列中是未来的?

huangapple go评论80阅读模式
英文:

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();

huangapple
  • 本文由 发表于 2023年7月31日 18:12:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76802604.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定