Display rows from MySQL where a datetime is within the next hour

Question

I always have trouble with complicated SQL queries.

This is what I have

$query = '
            SELECT id,
                   name, 
                   info, 
                   date_time
            FROM acms_events
                WHERE date_time = DATE_SUB(NOW(), INTERVAL 1 HOUR)
                    AND active = 1
            ORDER BY date_time ASC
            LIMIT 6
        ';

I want to get up to 6 rows that are upcoming within the hour. Is my query wrong? It does not seem to get events that are upcoming within the next hour when I test it.

What is the correct syntax for this?

Thanks

Answer 1

I'm going to postulate that you're looking at a group of records that contain a range of DATETIME values, so you probably want something more like this:

SELECT id,
       name, 
       info, 
       date_time
FROM acms_events
    WHERE date_time < DATE_ADD(NOW(), INTERVAL 1 HOUR)
        AND date_time >= NOW()
        AND active = 1
ORDER BY date_time ASC
LIMIT 6

Otherwise, your query is looking for records with a date_time of exactly "now + 1 hour". I'm assuming all your dates aren't specific to that particular second. ;)

To clarify a bit, DATE_ADD() and DATE_SUB() return exact timestamps, so your query above roughly translates to something like SELECT ... WHERE date_time = '2010-04-14 23:10:05' ORDER BY ..., which I don't think is what you want.

Answer 2

WHERE date_time = DATE_SUB(NOW(), INTERVAL 1 HOUR)

means date_time equals exactly now minus one hour, which would result in any record exactly one hour old.

Why not use

WHERE TIMEDIFF(date_time, NOW()) < '01:00:00'
AND date_time > NOW()