CODEWITHSUNDEEP
mysqlsql
eric dykstra
eric dykstra

Reputation: 279

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

Upvotes: 0

Views: 63

Answers (1)

JohnHC
JohnHC

Reputation: 11205

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)

Upvotes: 1

Related Questions