Mysql DATETIME related queries: how to calculate the expired entry?

Question

i have a table that has a cell containing a timestamp when the entry was created, than i have a cell containing the number of days that row should expire, something like this my_tb:

id  |  elem1   |   timestamp    |  maxdays   |  active
1   |  jondoe  |   2010-09-28   |    60      |     1
1   |  foo     |   2010-09-18   |    30      |     1
1   |  janedo  |   2010-09-08   |   120      |     1
1   |  bar     |   2010-08-28   |    30      |     0

maxdays is the value in days (60 = 2 months etc..)

i basically need a query to calculate when those entries expires

i though something like this but i dont know how to translate it in a real query (this below doesnt work)

SELECT * FROM my_tb WHERE timestamp+maxdays < NOW()

so basically with this query i can check if a row has exceeded the number of days (starting thetimestamp date) and then i can update the cell active to 0

Answer 1

Timestamp is an amount of of seconds.

One day equals 24 (hours) * 60 (minutes) * 60 (seconds) so your query should looks like:

SELECT * FROM my_tb WHERE timestamp + ( maxdays * 24 * 60 * 60 ) < NOW()

Answer 2

SELECT * FROM my_tb WHERE timestamp + INTERVAL maxdays DAY < CURRENT_TIMESTAMP;

Answer 3

You can do:

SELECT fields
  FROM my_tb
 WHERE DATEDIFF(timestamp, NOW()) < maxdays

If you just want to update the active field, you don't need a SELECT:

UPDATE my_tb
   SET active=0
 WHERE DATEDIFF(timestamp, NOW()) < maxdays

See: Manual entry on DATEDIFF()