eric dykstra eric dykstra - 5 months ago 20
MySQL Question

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

I have a table,

that has a
datetime field and a
integer field.

I want to query all
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.



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)