[RESOLVED] Simple timestamp question?

goaman

Hi,
My field called timestamp type is set to TIMESTAMP, and I'm trying to delete all the entries that are older than 15min..

I've tried to run this query several time.. but it seems like it's removing all the fresh entries. I wonder whats the problem.. Please let me know if u guys have noticed.. 🙂

$query = mysql_query("DELETE FROM activities WHERE `timestamp` > (NOW() - 900) ");

thanks..

Installer

Take a look at the MySQL manual section on DATE, DATETIME, and TIMESTAMP types. You'll see that the MySQL timestamp isn't the same as the Unix timestamp. Then check out the DATE_SUB() function. Then turn your "greater than" sign around.

goaman

Installer wrote:
Take a look at the MySQL manual section on DATE, DATETIME, and TIMESTAMP types. You'll see that the MySQL timestamp isn't the same as the Unix timestamp. Then check out the DATE_SUB() function. Then turn your "greater than" sign around.

Hi Installer,
thanks for replying. 🙂
I hope this is the right way to do... am I right?
I've checked.. and made little corrections..

$query = mysql_query("DELETE FROM activities WHERE `timestamp` < (UNIX_TIMESTAMP() - 900)");

Installer

Yeah, that looks like it's close to workable. Just one more change:

$query = "DELETE FROM activities WHERE UNIX_TIMESTAMP(`timestamp`) < (UNIX_TIMESTAMP() - 900)";

should work. Or:

$query = "DELETE FROM activities WHERE `timestamp` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)";

EDIT: My screwed-up code corrected (I hope).

goaman

Installer wrote:
Yeah, that looks like it's close to workable. Just one more change:
$query = "DELETE FROM activities WHERE UNIX_TIMESTAMP(`timestamp`) < (UNIX_TIMESTAMP() - 900)";
should work. Or:
$query = "DELETE FROM activities WHERE `timestamp` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)";
EDIT: My screwed-up code corrected (I hope).

Great, thanks Installer.. really appreciate it... working jst fine.. 🙂