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

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

Answer 2

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 3

I'd produce the timestamp in php, then pass it into your mysql query:

$date = new DateTime();
$date->modify("-10 minutes")

Answer 4

Your query is correct, if you have access to phpmyadmin execute the command in the SQL console that will give you some more information.

Answer 5

PHP Code :

//Your Database Details

$conn = mysqli_connect("localhost","root","","tifu");

//Actual Core thing

$delete_otp = mysqli_query($conn,"DELETE FROM temp_project_member_details WHERE create_at < ('" . date("Y-m-d H:i:s"). "' - INTERVAL 10 MINUTE)"); 

Note : Here temp_project_member_details : Table Name in the database tifu

and create_at : Field name inside the Table temp_project_member_details which has Date and Time stored using the same function '" . date("Y-m-d H:i:s"). "'

Screenshot of Deleting After 10 minute code

Screenshot of the storage of Date and Time in the create_at field of table as described in answer