CODEWITHSUNDEEP
sqlpostgresql
John Smith
John Smith

Reputation: 1886

Finding jobs to run now through a last_run and related interval in same query

I'm trying to write some cron-like software. I'm using Postgres.

CREATE TABLE "public"."crons" (
  "uuid" text COLLATE "pg_catalog"."default" NOT NULL,
  "last_run" timestamp(6),
  "frequency" int4 NOT NULL DEFAULT 1,
)

last_run is a recorded timestamp when the last job did complete. frequency is expressed in minutes, and can be 1, 5, you name it.

I would like to produce a query that selects the next jobs (rows) to run based on the current time from this table, but so far I'm not able to find out how. Do I need to add last_run to the frequency and check if the current timestamp has passed?

Any pointers? Thanks.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270533

Hmmm, you can get the next run time and order them:

select c.*
from crons c
order by c.last_run + c.frequency * interval '1 minute'

If you want those that have not yet run but should have:

select c.*
from crons c
where c.last_run + c.frequency * interval '1 minute' < now();

If you want the next runs that should occur but haven't yet:

select c.*
from crons c
where c.last_run + c.frequency * interval '1 minute' >= now()
order by c.last_run + c.frequency * interval '1 minute'

Upvotes: 2

Related Questions