How to calculate time difference in MYSQL

Question

I have Rails application which using MYSQL as database. For some condition, I have to delete all the records from table which was stored exactly 2 hours before the current time.

My query is :

DELETE FROM TABLE_NAME WHERE (NOW() - created_at) > 7200;

Here create_at is datetime column type. Storing the value in the format "2012-12-04 06:39:44"

My problem is, the above query fetch the records even though the record created time is just 40 to 50 minutes and got deleted. The only problem is the record got delete after it reach 40 to 50 minx from it create time.

Can any one please correct my query. I want the MySQL solution. Please help me

Answer 1

You probably need this if you want to delete records created exactly 2 hours ago:

DELETE FROM TABLE_NAME WHERE created_at = NOW() - INTERVAL 2 HOUR

or this, that will delete all records created more than 2 hours ago:

DELETE FROM TABLE_NAME WHERE created_at < NOW() - INTERVAL 2 HOUR

Answer 2

Try this ::

DELETE FROM TABLE_NAME WHERE TIMEDIFF(NOW(),created_at) < '02:00:00';

Answer 3

Try:

DELETE FROM TABLE_NAME WHERE created_at<DATE_SUB(NOW(),INTERVAL 2 HOUR)

This query will delete everything created MORE THAN 2 hours ago. Putting an equal sign would mean EXACTLY 2 hours ago (in second). Of course you can format date to consider only minutes, but that would slow down the query. If created_at is indexed (and I think it should be) don't perform any functions on it so it can use index to perform delete faster.