SQL查询将cron表达式转换为日期/时间格式 [英] SQL Query to convert cron expression to date/time format
问题描述
我在Linux主机上安排了一些cron作业.每个作业都将执行日志写入Oracle数据库表中.该表有一个结果列,可以用"OK"或"KO"来赋值.该表还具有一个时间戳列,该列的值与作业的最后执行时间相同.这样,我可以确定工作结果.现在,我需要确保该作业已实际执行.我还有另一个带有作业ID和cron表达式的表,例如:
I have some cron jobs scheduled on a Linux host. Each job writes execution log in an Oracle database table. The table has a result column that can be valued with 'OK' or 'KO'. The table also has a timestamp column that is valued with the last execution time for the job. In this way I'm sure about the job result. Now I need to be sure that the job has actually ran. I have another table with job ID and cron expression, for example:
JOB_ID SCHEDULE
102 00 09 * * *
如何编写sql select以对照SCHEDULE字段(cron表达式)检查上次执行时间?
How can I write a sql select to check the last execution time against the SCHEDULE field (cron expression)?
我将不胜感激任何建议,可以更改我的方法,但我想在数据库表中使用cron语法
I will appreciate any suggestion, I can change the my approach but I would like to use the cron syntax inside the database table
问候乔娃
推荐答案
您可以使用 regexp_substr
函数从计划元组中提取单个元素.例如, regexp_substr(schedule,'[0-9 *] +',1,1)
将提取分钟元素,而 regexp_substr(sched,'[0-9 *] +',1,2)
将提取小时元素.第四个参数选择所需的元素.然后,您可以使用 EXTRACT
或 TO_CHAR
函数获取时间戳的各个部分以进行比较.
You can use the regexp_substr
function to extract individual elements from the schedule tuple. For example regexp_substr(schedule, '[0-9*]+', 1,1)
will extract the minute element, while regexp_substr(sched, '[0-9*]+', 1, 2)
will extract the hour element. The 4th parameter selects the desired element. You can then use either the EXTRACT
or TO_CHAR
function to get at various portions of your timestamp for comparison.
with cron(ID, Sched) as (
select 102, '00 9 * * * *' from dual
), exec(id, ts) as (
select 102, to_timestamp('2017-11-05 9:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
select 102, to_timestamp('2017-11-05 9:05:00', 'yyyy-mm-dd hh24:mi:ss') from dual
), c2 as (
select id
, sched
, regexp_substr(sched,'[0-9*]+',1,1) min
, regexp_substr(sched,'[0-9*]+',1,2) hour
, regexp_substr(sched,'[0-9*]+',1,3) day
, regexp_substr(sched,'[0-9*]+',1,4) mon
, regexp_substr(sched,'[0-9*]+',1,5) wday
, regexp_substr(sched,'[0-9*]+',1,6) year
from cron
)
select c2.*
, exec.ts
, case when (year = '*' or to_number(to_char(ts,'yyyy')) = to_number(year))
and (mon = '*' or to_number(to_char(ts,'mm') ) = to_number(mon ))
and (day = '*' or to_number(to_char(ts,'dd') ) = to_number(day ))
and (hour = '*' or to_number(to_char(ts,'hh24')) = to_number(hour))
and (min = '*' or to_number(to_char(ts,'mi') ) = to_number(min ))
and (wday = '*' or to_number(to_char(ts,'d') ) = to_number(wday))
then 'OK'
else 'KO'
end Match
from exec
join c2
on c2.id = exec.id;
根据需要从case语句中部分或全部移动逻辑表达式,以获取所需的结果.
move the logical expression from the case statement in part or whole as needed to get the results you need.
这篇关于SQL查询将cron表达式转换为日期/时间格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!