select all records created within the hour

Question

startTimestamp < date_sub(curdate(), interval 1 hour)

Will the (sub)query above return all records created within the hour? If not will someone please show me a correct one? The complete query may look as follows:

select * from table where startTimestamp < date_sub(curdate(), interval 1 hour);

Answer 1

Rather than CURDATE(), use NOW() and use >= rather than < since you want timestamps to be greater than the timestamp from one hour ago. CURDATE() returns only the date portion, where NOW() returns both date and time.

startTimestamp >= date_sub(NOW(), interval 1 hour)

For example, in my timezone it is 12:28

SELECT NOW(), date_sub(NOW(), interval 1 hour);
2011-09-13 12:28:53  2011-09-13 11:28:53

All together, what you need is:

select * from table where startTimestamp >= date_sub(NOW(), interval 1 hour);