按日期选择条目 - >= NOW(), MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3114769/
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
Selecting entries by date - >= NOW(), MySQL
提问by NightMICU
I have a Calendar of Events table and I would like to select events with dates equal to or greater than today. When I use the following SELECT statement, it only retrieves events in the future (> NOW()):
我有一个事件日历表，我想选择日期等于或大于今天的事件。当我使用以下 SELECT 语句时，它只会检索未来的事件 (> NOW())：
<?php
$db->query("SELECT * FROM events WHERE event_date >= NOW()");
?>
How would I go about selecting all events that are either today or in the future?
我将如何选择今天或未来的所有事件？
Thank you
谢谢
回答by Sarfraz
回答by OMG Ponies
The reason that this query:
这个查询的原因：
SELECT * FROM events WHERE event_date >= NOW()
...returns records from the future, is because NOW() includes the time as well as the date. And that time portion indicates the time at which the [function or triggering] statement began to execute. So that means the start of the day in a DATETIME data type looks like: 2010-06-24 00:00:00(YYYY-MM-DD HH:MM:SS, to microsecond precision), which NOW() would show something like 2010-06-24 14:24:31...
...从未来返回记录，因为 NOW() 包括时间和日期。并且该时间部分表示 [function or triggering] 语句开始执行的时间。所以这意味着 DATETIME 数据类型中一天的开始看起来像：2010-06-24 00:00:00（YYYY-MM-DD HH:MM:SS，到微秒精度），NOW() 会显示类似2010-06-24 14:24:31......
Here are your options:
以下是您的选择：
SELECT * FROM events WHERE event_date >= DATE(NOW())
SELECT * FROM events WHERE event_date >= DATE(CURRENT_TIMESTAMP)
SELECT * FROM events WHERE event_date >= CURRENT_DATE()
SELECT * FROM events WHERE event_date >= CURRDATE()
回答by Akshat
You could also do
你也可以这样做
<?php
$db->query("SELECT * FROM events WHERE UNIX_TIMESTAMP(event_date) >= UNIX_TIMESTAMP(NOW())");
?>
Which is more accurate than using CURTIME() if by any chance your using time as well as date
如果您的使用时间和日期有任何机会，这比使用 CURTIME() 更准确
回答by Alex Martelli
If you care only about the date, not the time, use CURDATE()instead of NOW().
如果您只关心日期而不是时间，请使用CURDATE()而不是NOW().
Of course you can alternatively use the synonym CURRENT_DATE(with or without parentheses after it), but the docs I pointed to give CURDATEas the first spelling;-).
当然，您也可以使用同义词CURRENT_DATE（后面有括号或没有括号），但我指出的文档CURDATE作为第一个拼写给出；-)。
回答by Phil Wallach
Your issue is that now() is very accurate. So dates for today and before now because they started at the beginning of the day.
您的问题是 now() 非常准确。所以今天和之前的日期，因为它们是在一天开始的时候开始的。
Use this:
用这个：
select * from events where datediff(event_date, now()) >= 0;
回答by Kolob Canyon
Here's the difference between CURDATE and NOW:
这是 CURDATE 和 NOW 之间的区别：
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2017-03-17 |
+------------+
1 row in set (0.03 sec)
mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2017-03-17 09:04:45 |
+---------------------+
1 row in set (0.00 sec)
I always use NOW just to be accurate
我总是使用 NOW 只是为了准确
