Search code examples
phpmysqlphpbb

using an epoch unix timestamp in a mysql query

I'm trying to mark accounts inactive (user_type = '1') which haven't signed in (user_lastvisit) within the last 90 days as inactive.

user_type tells phpbb if the account is 0 = active or 1 = inactive

user_lastvisit is the last time the user signed into the phpbb board. It is stored in epoch time format.

The problem: I can't figure out how to add 90 days to user_lastvisit (stored in epoch format) and see if it's less than the current time.

$result = mysql_query("UPDATE phpbb_users SET user_type = '1' WHERE DATE_ADD(FROM_UNIXTIME(user_lastvisit), INTERVAL 90 DAY) < NOW() AND user_type = '0'")
or die(mysql_error());

Update: I edited the query based on the suggestion below.

Solution

  • Take a look at FROM_UNIXTIME and DATE_ADD. With those two functions you should be able to convert the timestamp to a datetime and then use it to add 90 Days. Example:

    SELECT * FROM yourtable WHERE DATE_ADD(FROM_UNIXTIME(user_lastvisit), INTERVAL 90 DAY) > NOW()