CODEWITHSUNDEEP
mysqlsql
Curtis
Curtis

Reputation: 2704

create interval in query and return if valid

I've basically got a mySQL table with the following columns:

time which is int(11) and will hold a specified value (an amount of seconds)

package which is int(11) and will hold another specified value (an amount of actions to be performed over a day)

last_run which is int(11) and will hold a datetime value on which the SELECT query row was last used

An example of the table would be like so

time     |     package    |        last_run
--------------------------------------------------
86400    |      1000      |    0000-00-00 00:00:00
86400    |      1000      |    0000-00-00 00:00:00
86400    |      1000      |    0000-00-00 00:00:00

I want to basically do a SELECT query like the following

SELECT 
    * 
FROM 
    `table` 
WHERE 
    `divide time by package` AS total_seconds_between_each_run 
AND 
    `last_run` OLDER THAN (NOW() / total_seconds_each_run)

so the idea I want to do is only grab rows which are older than the total_seconds_between_each_run as I only want an action be performed every specified interval

I understand the syntax is not correct on the mySQL query, but that's the "example" of which I would like to do if possible using mySQL

EDIT

Would something like the following work?

(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(last_run)) > (time / package)

Upvotes: 0

Views: 44

Answers (1)

Barmar
Barmar

Reputation: 781058

You don't want to divide NOW() by seconds per run -- you already did the division when you calculated that. You want to subtract.

SELECT *
FROM table
WHERE last_run < DATE_SUB(NOW(), INTERVAL (time/package) SECOND)

Your calculation in the EDIT using UNIX_TIMESTAMP is equivalent, so it should work.

Upvotes: 1

Related Questions