CODEWITHSUNDEEP
mysqlsql
Gadgetster
Gadgetster

Reputation: 483

Value of time of the beginning of today

I am trying to get the number of times a user has logged in since the beginning of each day.

SELECT user_id 
    FROM users 
    WHERE TIMESTAMPDIFF(HOUR, user_login, NOW()) < 12 
        AND user_id = 1

This sql checks the difference between the login and the current time which is wrong. What should go instead of NOW() to indicate midnight+1min of that day and check the logins throughout that day?

Upvotes: 1

Views: 50

Answers (1)

Nicolas Zimmer
Nicolas Zimmer

Reputation: 424

As far as I understand your code, the question is rather about mySQL and not PHP. Use

CURDATE()

instead of NOW() since TIMESTAMPDIFF will use this as 'Current Day, 00:00:00'

To get the end of the current day use

CURDATE() + INTERVAL 1 DAY

so your where-clause should be

WHERE user_login BETWEEN CURDATE() AND CURDATE() + INTERVAL 1 DAY

provided you have the user_login field in the DATE format, otherwise you would have to cast that accordingly.

Upvotes: 1

Related Questions