eric dykstra eric dykstra - 2 months ago 6
MySQL Question

SQL: How do I query based on a dynamic date calculation?

I have a table,

foos
that has a
last_checked_at
datetime field and a
checking_inverval
integer field.

I want to query all
foos
that haven't been checked since the checking interval. I've tried variations on the following:

SELECT `foos`.* FROM `foos`
WHERE (`foos.checking_interval` < TIMESTAMPDIFF(SECOND, `foos.last_checked_at`, NOW())


But I have yet to have any success. Or maybe I'm barking down the wrong tree altogether, and should just have another column that has the calculated datetime and let my application handle setting that date.

Thanks

Answer

So, where the last checked at is earlier than now minus the interval?

FOOS foos.*
FROM foos
WHERE foos.last_checked_at < date_sub(now(),INTERVAL foos.checking_interval SECOND)
Comments