MySQL Delete Records Older Than X Minutes?

Question

I've searched quite a bit and found a few solutions that did not end up working for me and can't understand why.

I have a table with a timestamp column. The MySQL type for this column is 'datetime'. I insert into this table the following from PHP.

date('Y-m-d H:i:s')

This enters, what looks like the correct value for the MySQL date time.

2012-06-28 15:31:46

I want to use this column to delete rows that are older than, say, 10 minutes. I'm running the following query, but it's not working. It affects 0 rows.

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

Can anyone shed some light as to what I'm doing wrong and why it's not working properly?

Thanks.

Answer 1

Since TIMESTAMP() is a built-in function that returns the current time stamp, your query will never return any results.

Try wrapping the column in back ticks to let MySQL know you mean the column, not the reserved word:

DELETE FROM adminLoginLog WHERE `timestamp` < (NOW() - INTERVAL 10 MINUTE);

Answer 2

timestamp is a reserved keyword in mysql. To use timestamp as a field name, you have to put that in backticks as shown below.

`timestamp`

If time_created is a unix timestamp (int), you should be able to use something like this:

DELETE FROM adminLoginLog WHERE `timestamp` < (UNIX_TIMESTAMP() - 600);

(600 seconds = 10 minutes - obviously)

Otherwise (if time_created is mysql timestamp), you could try this:

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

Update 1

DELETE FROM adminLoginLog WHERE `timestamp` < DATE_SUB( CURRENT_TIME(), INTERVAL 10 MINUTE)

Update 2

DELETE FROM adminLoginLog WHERE `timestamp` < DATE_SUB( NOW(), INTERVAL 10 MINUTE)

Demo