MySQL Query to delete rows whose timestamp is older than current timestamp

mysql delete-row
Arihant picture Arihant · Apr 17, 2012 · Viewed 41.2k times · Source

I am looking for a query that is able to delete all rows from a table in a database where timestamp is older than the current date/time or current timestamp.

Would really appreciate some help out here urgently!

Here's the query I am using but as I thought it ain't working:

delete from events where timestamp<CURRENT_TIMESTAMP{);

Answer

Dave Morton picture Dave Morton · Nov 19, 2012

Um... This may seem silly, but every record in the table will be older than Now(), since Now() is calculated at the time that query is processed. If you you want to delete a record that's older than another record, then you don't want to use Now(), but the timestamp from the record you're comparing the rest to. Or, if you want to delete records that are older than a specific point in time, then you need to calculate the timestamp that you want to use to compare against. For example, to delete records older than 10 minutes, you could use this:

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 10 MINUTE)

Or, for deleting records that are over a day old:

DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 DAY)

For specific points in time (e.g. Oct. 12th, 2012 at 4:15:00 PM GMT), there's a method to do that, but the syntax escapes me, right now. Where's my MySQL manual? :)

Related questions

How to delete a certain row from mysql table with same column values?

I have a problem with my queries in MySQL. My table has 4 columns and it looks something like this: id_users id_product quantity date 1 2 1 2013 1 2 1 2013 2 2 1 2013 1 3 1 2013 id_users and id_product are foreign keys from different tables. What I want is …

Read More
SQL DELETE with JOIN another table for WHERE condition

I have to delete rows from guide_category that have no relation with guide table (dead relations). Here is what I want to do, but it of course does not work. DELETE FROM guide_category AS pgc WHERE pgc.id_…

Read More
Deleting records before a certain date

How would I go about deleting all records from a MySQL table from before a certain date, where the date column is in DATETIME format? An example datetime is 2011-09-21 08:21:22.

Read More