CODEWITHSUNDEEP
phpmysql
AndrewFerrara
AndrewFerrara

Reputation: 2403

Mysql delete by difference in time()

I want to delete every row where the time difference is 5 minutes. I'm using time() as an int.

mysql_query("DELETE FROM users WHERE time()-time > 300");

the query is the problem...

Upvotes: 3

Views: 578

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

Assuming the time is a column in the users table, this is what you need:

mysql_query("DELETE FROM users WHERE NOW() - INTERVAL 300 SECOND > time");

NOW() is a function that brings back the time on the DB server rather than the web server.

Just make sure the clocks on the DB server and Web server are sync'd

Here is a sample using SELECT

mysql> select now(),(now() - INTERVAL 300 SECOND);
+---------------------+-------------------------------+
| now()               | (now() - INTERVAL 300 SECOND) |
+---------------------+-------------------------------+
| 2011-04-15 16:21:41 | 2011-04-15 16:16:41           |
+---------------------+-------------------------------+

Upvotes: 4

user680786
user680786

Reputation:

mysql_query("DELETE FROM users WHERE time < '".(time()-300)."' ");
and this query will be cached by mysql

Upvotes: 0

Pascal MARTIN
Pascal MARTIN

Reputation: 401182

time() is a PHP function -- and has absolutely no meaning in SQL.

You should either :

  • use one of MySQL's datetime functions
    • To get an UNIX Timestamp, I suppose unix_timestamp(now()) might do the trick.
  • or get time() out of the SQL query, so it's evaluated by PHP.


For example, using the second idea, I suppose you could do something like this :

mysql_query("DELETE FROM users WHERE " . time() . "-time > 300");

Upvotes: 5

Related Questions