eric dykstra eric dykstra - 1 year ago 67
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.


Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download