mysql interval weekday,Mysql DATE_SUB（NOW（），INTERVAL 1天）24小时或工作日？

最新推荐文章于 2024-08-01 07:15:00 发布

冰上行走

最新推荐文章于 2024-08-01 07:15:00 发布

阅读量943

点赞数

文章标签： mysql interval weekday

本文介绍了一种使用SQL查询来精确统计过去24小时内注册用户的高效方法。通过对比用户注册时间与当前日期之间的关系，利用BETWEEN操作符确定了有效的日期范围。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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?

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

解决方案

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,

FROM users

WHERE DATE(lastModified) = DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )

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

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

SELECT

name,

FROM users

WHERE lastModified

BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )

AND DATE ( NOW() )