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");

th开发者_StackOverflowe query is the problem...

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");

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           |
+---------------------+-------------------------------+

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