Mysql Date_sub(now(), Interval 1 Day) 24 Hours Or Weekday?

I am trying to get the total amount of registered users per day. At the moment I am using this: $sql = 'SELECT name, email FROM users WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) < las

Solution 1:

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
WHEREDATE(lastModified) =DATE( DATE_SUB( NOW() , INTERVAL1DAY ) )

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,
    email
FROM users
WHERE lastModified 
    BETWEENDATE( DATE_SUB( NOW() , INTERVAL1DAY ) )
    ANDDATE ( NOW() )

Solution 2:

I think you need

SELECT
    name,
    email
FROM users
WHEREDATE(lastModified) =DATE( NOW() )

This effectively "rounds to the date only" and will therefore only match records "since midnight".