sql records in last few hours

Question

I have records in mysql table and I want to know how many records were in last 3, 7 and 12 hours. What would be the sql for that? My date field in in the format as

2010-08-09 09:52:27 
2010-08-09 09:52:27 
2010-08-09 09:52:27 
2010-08-09 10:44:46 
2010-08-09 10:44:46 
2010-08-09 11:58:27 
2010-08-09 14:48:22 
2010-08-09 14:48:22 

Answer 1

For the last three hours:

WHERE column BETWEEN DATE_SUB(NOW(), INTERVAL 3 HOUR)
                 AND NOW()

For the last seven hours:

WHERE column BETWEEN DATE_SUB(NOW(), INTERVAL 7 HOUR)
                 AND NOW()

For the last twelve hours:

WHERE column BETWEEN DATE_SUB(NOW(), INTERVAL 12 HOUR)
                 AND NOW()

If you're wanting to specify a data as a string, use:

WHERE column BETWEEN DATE_SUB(STR_TO_DATE('2010-07-08 07:57:45', '%Y-%m-%d %T'), INTERVAL 3 HOUR)
                 AND STR_TO_DATE('2010-07-08 07:57:45', '%Y-%m-%d %T')

...though honestly, you could use this just as well:

WHERE column BETWEEN STR_TO_DATE('2010-07-08 04:57:45', '%Y-%m-%d %T')
                 AND STR_TO_DATE('2010-07-08 07:57:45', '%Y-%m-%d %T')

If that doesn't work, then I wonder if the column is storing values as strings/varchar rather than DATETIME.

Reference:

• DATE_SUB

Answer 2

Assuming that no records bear a timestamp in the future, you could use:

WHERE myDateField > DATE_SUB(NOW(), INTERVAL 3 HOUR)

for the last three hours-worth of records, and similarly for 7 and 12 hours.