Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 792
alex Avatar asked Mar 26 '13 09:03

alex


People also ask

How does MySQL calculate time intervals?

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.

What is Date_sub in MySQL?

The DATE_SUB() function subtracts a time/date interval from a date and then returns the date.

What is interval in MySQL?

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.

How do I subtract one day from a date in MySQL?

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.


2 Answers

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() )
like image 158
Simon at My School Portal Avatar answered Nov 07 '22 08:11

Simon at My School Portal


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".

like image 20
Floris Avatar answered Nov 07 '22 06:11

Floris