CODEWITHSUNDEEP
phpmysqlsql
user3624130
user3624130

Reputation: 37

Delete rows older than 30 minutes in MySQL

In my table mytable. I have a column named session_time (float) where I store the time in microseconds when the row was added (I use the PHP function microtime(true) to get a float). How can I delete all rows from my table mytable which are older than 20 minutes?

I already tried something like this: 

DELETE FROM mytable WHERE session_time < DATE_SUB(NOW(), INTERVAL 1800 SECOND)

But this deletes all rows!

Upvotes: 0

Views: 4213

Answers (4)

Abhishek Goyal
Abhishek Goyal

Reputation: 877

UPDATED...

Instead of using php microtime(true) , you should use MySQL function NOW(). Delete the old column, and make a new one : 

ALTER TABLE your_table ADD session_time DATETIME NOT NULL DEFAULT NOW();

Now when you add a row, it's session_time will be set to the current time automatically, and you do not need to insert any value for session_time.

Now for deleting all rows older than 30 minutes, use : 

DELETE FROM your_table WHERE TIMESTAMPDIFF(MINUTE,session_time,NOW()) > 30;

Upvotes: 2

Devon Bessemer
Devon Bessemer

Reputation: 35367

PHP microtime() uses unix timestamp format, not the DATETIME format. If you are storing things like that, you could use PHP to fill the query or use MySQL's unix_timestamp() function.

For PHP, it would be as simple as:

$query = 'DELETE FROM mytable WHERE session_time < '.time() - 1800;

I would recommend just using the DATETIME format on session_time though, you should take advantage of MySQL’s storage types.

Upvotes: 0

Josh from Qaribou
Josh from Qaribou

Reputation: 6908

It sounds like you're comparing a microsecond value generated in PHP, to a timestamp value generated in mysql. There's a few things that could go wrong here, since you may not actually be comparing time to time but time to float, or your PHP app is using a different clock than your mysql.

Since you're doing the comparison in SQL, you should save the session_time value based on SQL too. This is a good place for a default value, e.g.:

mysql> create table mytable ( id int not null, session_time timestamp default now() );

Upvotes: 0

Preslav Panayotov
Preslav Panayotov

Reputation: 312

Try 

DELETE FROM  myTABLE  WHERE session_time < (now() - interval 30 minute)

I didn't test it. I think it shoud work as well.

Upvotes: 1

Related Questions