Mysql DATE_SUB（NOW（），INTERVAL 1天）24小时或工作日？ [英] Mysql DATE_SUB(NOW(), INTERVAL 1 DAY) 24 hours or weekday?

问题描述

我正在尝试每天获得最多数量的注册用户。目前我正在使用这个

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"

但我不知道这是每天还是每24小时工作？

but i am not sure if this works per day or per 24 hours?

例如，22小时前注册的用户不应该退回，就像今天（=星期二）的用户

For example, a user who registered 22 hours ago shouldn't be returned i just like the user of today(=Tuesday)

推荐答案

lastModified是可能是datetime。要将其转换为日期，您可以在DATE（）即 DATE（lastModified）中简单地将其转换。 DATE（）返回当天有效00:00的日期时间值的日期部分。

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.

SELECT
    name,
    email
FROM users
WHERE DATE(lastModified) = DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )

使用这个来匹配一个 WHERE 效率低下，因为所有行都需要将DATE应用于它们，因此它可能扫描整个表。将 lastModified 与您要查找的上限和下限进行比较更为有效，在这种情况下，> = 00:00 SUBDATE（NOW ），INTERVAL 1 DAY）和< 00:00 on NOW（）

Using this to match a WHERE though 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 lastModified to 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()

因此，您可以使用BETWEEN进行选择，给出以下内容。 p>

Therefore you can use BETWEEN to make your select giving the following.

SELECT
    name,
    email
FROM users
WHERE lastModified 
    BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )
    AND DATE ( NOW() )

这篇关于Mysql DATE_SUB（NOW（），INTERVAL 1天）24小时或工作日？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！