How to calculate time difference in MYSQL

Question

I have a Rails application that uses MYSQL as a database. For some conditions, I have to delete all records from the table that were stored exactly 2 hours before the current time.

My request:

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

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

My problem is that the above query retrieves the records, even if the record creation time is only 40-50 minutes and deleted. The only problem is that the record was deleted after it reached 40-50 minutes to create time.

Can someone fix my request. I want a MySQL solution. Please help me

Answer 1

You will 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 the following:

 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 request will delete everything created MORE THAN 2 hours ago. An equal sign will be EXACTLY 2 hours ago (in the second). Of course, you can format the date to only consider minutes, but this will slow down the request. If created_at is indexed (and I think it should be), do not perform any functions on it in order to use the index faster to perform the deletion.

Answer 4

I understand that you want to delete all records created in a period of time. Thus, you should not apply the "more" operator to the subtraction operation. Instead, you should try to specify the assigned time interval.

You can also take a look at the timediff function http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

Sorry, I cannot post the correct statement for you, since I do not have a mysql server.