We have a Java application that executes several types of jobs at various schedule time using the Quartz scheduler. The application stores the job execution information in a table. For each job it's schedule is stored in another table in quartz cron expression format.
The tables can be represented as,
- jobs:
id, job_name, schedule_expression - job_histories:
id, job_id, status, created_at, updated_at.
Now I want to build a SQL query in Postgres and show for each job job_name, last_triggered_at, next_expected_at. In here the last_triggered_at will be the latest SUCCESSFUL job's created_at from job_histories table. My question is, how can I calculate the next_expected_at in SQL itself by considering the cron expression and last_triggered_at?
In the Java we would have calculated the next expected time using,
Date previousJobCreatedAt = Date.from(LocalDateTime.parse("2024-01-19T10:00:00").toInstant(ZoneOffset.UTC));
CronExpression expression = new CronExpression("0 0 12 * * ?");
Date nextExpectedAt = expression.getNextValidTimeAfter(previousJobCreatedAt);
System.out.println(nextExpectedAt);
I imagine the SQL would almost look like,
with ranked as (
select job_name,
schedule_expression,
created_at,
row_number() over(partition by job_id order by created_at desc) as rn
from jobs j
join job_histories jh on j.id = jh.job_id
where jh.status='SUCCESSFULL'
)
select job_name,
created_at as last_triggered_at,
<calculate next expected at by using schedule_expression & last_triggered_at>
from ranked
where rn=1
This SQL report will be shown in a BI tool Redash (similar to Tableau or Apache Superset).