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) < 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 want the user of today(=Tuesday).
The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
The DATE_SUB() function subtracts a time/date interval from a date and then returns the date.
MySQL interval is an operator, which is based on the binary search algorithm to search the items and returns the value from 0 to N. It is mainly used to calculate the date and time values. We can use the following syntax to create an interval value: INTERVAL expr unit.
To get yesterday's date, you need to subtract one day from today's date. Use CURDATE() to get today's date. In MySQL, you can subtract any date interval using the DATE_SUB() function. Here, since you need to subtract one day, you use DATE_SUB(CURDATE(), INTERVAL 1 DAY) to get yesterday's date.
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 ) )
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
BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )
AND DATE ( NOW() )
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".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With