Mysql DATE_SUB(NOW(), INTERVAL 1 DAY) 24 小时还是工作日？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15633653/
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
Mysql DATE_SUB(NOW(), INTERVAL 1 DAY) 24 hours or weekday?
提问by alex
I am trying to get the totaal amount of registerd users per day. At the moment i am using this
我正在尝试获取每天的注册用户总数。目前我正在使用这个
$sql = "SELECT name, email FROM users WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) < lastModified"
but i am not sure if this works per day or per 24 hours?
但我不确定这是否每天或每 24 小时有效？
For example, a user who registered 22 hours ago shouldn't be returned i just like the user of today(=Tuesday)
例如，22小时前注册的用户不应该像今天（=星期二）的用户一样返回
回答by Simon at My School Portal
lastModified is, presumably, a datetime. To convert this into a date you can simply wrap it in DATE() i.e. DATE(lastModified). DATE()returns the date part of a datetime value which is effectively 00:00 on that day.
lastModified 大概是一个日期时间。要将其转换为日期，您可以简单地将其包装在 DATE() ie 中DATE(lastModified)。DATE()返回日期时间值的日期部分，该值实际上是当天的 00:00。
SELECT
name,
email
FROM users
WHERE DATE(lastModified) = DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )
Using this to match a WHEREthough would be inefficient as all rows would require DATE applied to them and so it would probably scan the whole table. It is more efficient to compare lastModifiedto the upper and lower bounds you are looking for, in this case >= 00:00 on SUBDATE(NOW(),INTERVAL 1 DAY)and < 00:00 on NOW()
使用它来匹配WHERE虽然效率低下，因为所有行都需要将 DATE 应用于它们，因此它可能会扫描整个表。比较lastModified您正在寻找的上限和下限更有效，在这种情况下 >= 00:00 onSUBDATE(NOW(),INTERVAL 1 DAY)和 < 00:00 onNOW()
Therefore you can use BETWEEN to make your select giving the following.
因此，您可以使用 BETWEEN 进行选择，并给出以下内容。
SELECT
name,
email
FROM users
WHERE lastModified
BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )
AND DATE ( NOW() )
回答by Floris
I think you need
我想你需要
SELECT
name,
email
FROM users
WHERE DATE(lastModified) = DATE( NOW() )
This effectively "rounds to the date only" and will therefore only match records "since midnight".
这有效地“仅舍入到日期”，因此只会匹配“自午夜以来”的记录。
