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