Query to select all users who registered during one specific hour range

Question

I'm trying to do something with a MySQL query and am requesting your help for this one.

In fact, I want to send a daily email (with a cron task) to the users who received a notification during the day on my website. I already have this query which is the following:

SELECT u.username, u.email, COUNT(*) AS nb_notification
FROM notification n, user u
WHERE n.to_user_id = u.id
AND u.status = 1
AND n.date >= now() - INTERVAL 1 DAY
AND n.status = 0
GROUP BY u.username

Now, I would like to improve this query so all the emails are not sent during the same hour. To do that, I had the following idea: getting only the users who registered during the same hour the cron task is executed.

The register date of a user is stored in the user table under the name "register_date" as a DATETIME field.

For example, if I have two users:

• The user Alex registered on 09/10/2015 at 10:10am
• The user Sophia registered the same date at 08:52am

1. My cron task is executed at 11:20am... So no email are sent because there are no users who registered between 11:00am and 12:00am

   2. My cron task is executed at 10:00am... If Alex received some notifications in the last 24 hours, an email is sent!

   3. My cron task is executed at 08

How can I update my query to get only the users who registered at the same hour my cron task is executed?

Answer 1

SELECT u.username, u.email, COUNT(*) AS nb_notification
FROM notification n, user u
WHERE n.to_user_id = u.id
AND u.status = 1
AND n.date >= DATE_SUB(NOW(),INTERVAL 1 HOUR)
AND n.status = 0
GROUP BY u.username

Answer 2

Found the solution by myself, here is the MySQL query I was looking for:

SELECT u.username, u.email, COUNT(*) AS nb_notification
FROM notification n, user u
WHERE n.to_user_id = u.id
AND u.status = 1
AND n.date >= now() - INTERVAL 1 DAY
AND n.status = 0
AND HOUR(u.register_date) = HOUR(NOW())
GROUP BY u.username