在SQL中计算CRON表达式的下一个运行时间

Question

我在我的网站上使用CRON UI来为一些用户任务创建定期计划。该UI生成CRON格式，存储在数据库中，我正在寻找一种方法来获取下一个运行时间，以确定任务是否到期并相应地执行。是否有一种方法可以将CRON格式转换为SQL以获取下一个运行时间？

0 9 1-7 * 1 *                       First Monday of each month, at 9 a.m.
0 0 1 * * *                         At midnight, on the first day of each month
* 0-11 * * *                        Each minute before midday

表格

JobID, CRONSchedule
1  0 9 1-7 * 1 *
2  0 0 1 * * * 
3  * 0-11 * * * 

SQL

Select JobID, CRONSchedule, NextRunTime from TABLE

- Priyanka Rathee

---

也许这个链接可以帮到你：https://dev59.com/MnLYa4cB1Zd3GeqPXWlp - undefined

不，这只是一个日期时间格式。在这里，我正在寻找一个将其转换为日期并与getdate()进行比较以找到下一次运行时间的函数。 - undefined

Answer 1

我使用这个查询来确定特定的cron是否准备好运行（返回t/f）。根据这个查询，您可以获得下一次运行的日期和时间，更新表中的列，并可以执行各种任务。但是这段代码有一些限制，我们只能输入具有5位数字结构的cron数据。

* * * * * command
| | | | |
| | | | +----- Day of the week (0 - 6) (Sunday is both 0 and 7)
| | | +------- Month (1 - 12)
| | +--------- Day of the month (1 - 31)
| +----------- Hour (0 - 23)
+------------- Minute (0 - 59)

这将是我的表结构。

 Column |            Type             | Collation | Nullable |               Default                | Storage  | Compression | Stats target | Description 
--------+-----------------------------+-----------+----------+--------------------------------------+----------+-------------+--------------+-------------
 timeid | integer                     |           | not null | nextval('time_timeid_seq'::regclass) | plain    |             |              | 
 time   | timestamp without time zone |           |          |                                      | plain    |             |              | 
 cron   | character varying           |           |          |                                      | extended |             |              | 

WITH TABLE_1 AS (
    SELECT
        TIMEID,
        TIME,
        CRON,
        EXTRACT(MINUTE FROM now()) AS TIME_MINUTE,
        EXTRACT(HOUR FROM now()) AS TIME_HOUR,
        EXTRACT(DAY FROM now()) AS TIME_DAY_OF_MONTH,
        EXTRACT(MONTH FROM now()) AS TIME_MONTH,
        EXTRACT(DOW FROM now()) AS TIME_DAY_OF_WEEK,
        (STRING_TO_ARRAY(CRON,' '))[1] AS CRON_MINUTE,
        (STRING_TO_ARRAY(CRON,' '))[2] AS CRON_HOUR,
        (STRING_TO_ARRAY(CRON,' '))[3] AS CRON_DAY_OF_MONTH,
        (STRING_TO_ARRAY(CRON,' '))[4] AS CRON_MONTH,
        (STRING_TO_ARRAY(CRON,' '))[5] AS CRON_DAY_OF_WEEK,
        CASE
            WHEN SPLIT_PART(CRON, ' ', 1) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 1) FROM 3)::INT
            WHEN SPLIT_PART(CRON, ' ', 1) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 1) AS INT)
            WHEN SPLIT_PART(CRON, ' ', 1) = '*' THEN 0
            WHEN SPLIT_PART(CRON, ' ', 1) ~ '^[0-9]+-[0-9]+$' THEN (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 1), '-'))[1]::INT * 100 + (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 1), '-'))[2]::INT
            ELSE NULL
        END AS EXTRACTED_CRON_MINUTE_VALUE,
        CASE
            WHEN SPLIT_PART(CRON, ' ', 2) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 2) FROM 3)::INT
            WHEN SPLIT_PART(CRON, ' ', 2) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 2) AS INT)
            WHEN SPLIT_PART(CRON, ' ', 2) = '*' THEN 0
            WHEN SPLIT_PART(CRON, ' ', 2) ~ '^[0-9]+-[0-9]+$' THEN (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 2), '-'))[1]::INT * 100 + (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 2), '-'))[2]::INT
            ELSE NULL
        END AS EXTRACTED_CRON_HOUR_VALUE,
        CASE
            WHEN SPLIT_PART(CRON, ' ', 3) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 3) FROM 3)::INT
            WHEN SPLIT_PART(CRON, ' ', 3) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 3) AS INT)
            WHEN SPLIT_PART(CRON, ' ', 3) = '*' THEN 0
            WHEN SPLIT_PART(CRON, ' ', 3) ~ '^[0-9]+-[0-9]+$' THEN (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 3), '-'))[1]::INT * 100 + (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 3), '-'))[2]::INT
            ELSE NULL
        END AS EXTRACTED_CRON_DAY_OF_MONTH_VALUE,
        CASE
            WHEN SPLIT_PART(CRON, ' ', 4) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 4) FROM 3)::INT
            WHEN SPLIT_PART(CRON, ' ', 4) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 4) AS INT)
            WHEN SPLIT_PART(CRON, ' ', 4) = '*' THEN 0
            WHEN SPLIT_PART(CRON, ' ', 4) ~ '^[0-9]+-[0-9]+$' THEN (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 4), '-'))[1]::INT * 100 + (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 4), '-'))[2]::INT
            ELSE NULL
        END AS EXTRACTED_CRON_MONTH_VALUE,
        CASE
            WHEN SPLIT_PART(CRON, ' ', 5) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 5) FROM 3)::INT
            WHEN SPLIT_PART(CRON, ' ', 5) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 5) AS INT)
            WHEN SPLIT_PART(CRON, ' ', 5) = '*' THEN 0
            WHEN SPLIT_PART(CRON, ' ', 5) ~ '^[0-9]+-[0-9]+$' THEN (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 5), '-'))[1]::INT * 100 + (STRING_TO_ARRAY(SPLIT_PART(CRON, ' ', 5), '-'))[2]::INT
            ELSE NULL
        END AS EXTRACTED_CRON_DAY_OF_WEEK_VALUE
    FROM
        TIME
), TABLE_2 AS (
    SELECT
        TIMEID,
        TIME,
        CRON,
        TIME_MINUTE,
        TIME_HOUR,
        TIME_DAY_OF_MONTH,
        TIME_MONTH,
        TIME_DAY_OF_WEEK,
        EXTRACTED_CRON_MINUTE_VALUE,
        EXTRACTED_CRON_HOUR_VALUE,
        EXTRACTED_CRON_DAY_OF_MONTH_VALUE,
        EXTRACTED_CRON_MONTH_VALUE,
        EXTRACTED_CRON_DAY_OF_WEEK_VALUE,
        CASE
            WHEN CRON_MINUTE LIKE '*/%' AND TIME_MINUTE % EXTRACTED_CRON_MINUTE_VALUE = 0 THEN TRUE
            WHEN CRON_MINUTE ~ '^[0-9]+$' AND TIME_MINUTE - EXTRACTED_CRON_MINUTE_VALUE = 0 THEN TRUE
            WHEN CRON_MINUTE = '*' THEN TRUE
            WHEN CRON_MINUTE ~ '^[0-9]+-[0-9]+$' AND
                TIME_MINUTE = EXTRACTED_CRON_MINUTE_VALUE / 100 OR TIME_MINUTE = EXTRACTED_CRON_MINUTE_VALUE % 10 OR (TIME_MINUTE BETWEEN EXTRACTED_CRON_MINUTE_VALUE / 100
                AND EXTRACTED_CRON_MINUTE_VALUE % 10) THEN TRUE
            ELSE FALSE
        END AS RESULT_MINUTE,
        CASE
            WHEN CRON_HOUR LIKE '*/%' AND TIME_HOUR % EXTRACTED_CRON_HOUR_VALUE = 0 THEN TRUE
            WHEN CRON_HOUR ~ '^[0-9]+$' AND TIME_HOUR - EXTRACTED_CRON_HOUR_VALUE = 0 THEN TRUE
            WHEN CRON_HOUR = '*' THEN TRUE
            WHEN CRON_HOUR ~ '^[0-9]+-[0-9]+$' AND
                TIME_HOUR = EXTRACTED_CRON_HOUR_VALUE / 100 OR TIME_HOUR = EXTRACTED_CRON_HOUR_VALUE % 10 OR (TIME_HOUR BETWEEN EXTRACTED_CRON_HOUR_VALUE / 100
                AND EXTRACTED_CRON_HOUR_VALUE % 10) THEN TRUE
            ELSE FALSE
        END AS RESULT_HOUR,
        CASE
            WHEN CRON_DAY_OF_MONTH LIKE '*/%' AND TIME_DAY_OF_MONTH % EXTRACTED_CRON_DAY_OF_MONTH_VALUE = 0 THEN TRUE
            WHEN CRON_DAY_OF_MONTH ~ '^[0-9]+$' AND TIME_DAY_OF_MONTH - EXTRACTED_CRON_DAY_OF_MONTH_VALUE = 0 THEN TRUE
            WHEN CRON_DAY_OF_MONTH = '*' THEN TRUE
            WHEN CRON_DAY_OF_MONTH ~ '^[0-9]+-[0-9]+$' AND
                TIME_DAY_OF_MONTH = EXTRACTED_CRON_DAY_OF_MONTH_VALUE / 100 OR TIME_DAY_OF_MONTH = EXTRACTED_CRON_DAY_OF_MONTH_VALUE % 10 OR (TIME_DAY_OF_MONTH BETWEEN EXTRACTED_CRON_DAY_OF_MONTH_VALUE / 100
                AND EXTRACTED_CRON_DAY_OF_MONTH_VALUE % 10) THEN TRUE
            ELSE FALSE
        END AS RESULT_DAY_OF_MONTH,
        CASE
            WHEN CRON_MONTH LIKE '*/%' AND TIME_MONTH % EXTRACTED_CRON_MONTH_VALUE = 0 THEN TRUE
            WHEN CRON_MONTH ~ '^[0-9]+$' AND TIME_MONTH - EXTRACTED_CRON_MONTH_VALUE = 0 THEN TRUE
            WHEN CRON_MONTH = '*' THEN TRUE
            WHEN CRON_MONTH ~ '^[0-9]+-[0-9]+$' AND
                TIME_MONTH = EXTRACTED_CRON_MONTH_VALUE / 100 OR TIME_MONTH = EXTRACTED_CRON_MONTH_VALUE % 10 OR (TIME_MONTH BETWEEN EXTRACTED_CRON_MONTH_VALUE / 100
                AND EXTRACTED_CRON_MONTH_VALUE % 10) THEN TRUE
            ELSE FALSE
        END AS RESULT_MONTH,
        CASE
            WHEN CRON_DAY_OF_WEEK LIKE '*/%' AND TIME_DAY_OF_WEEK % EXTRACTED_CRON_DAY_OF_WEEK_VALUE = 0 THEN TRUE
            WHEN CRON_DAY_OF_WEEK ~ '^[0-9]+$' AND TIME_DAY_OF_WEEK - EXTRACTED_CRON_DAY_OF_WEEK_VALUE = 0 THEN TRUE
            WHEN CRON_DAY_OF_WEEK = '*' THEN TRUE
            WHEN CRON_DAY_OF_WEEK ~ '^[0-9]+-[0-9]+$' AND
                TIME_DAY_OF_WEEK = EXTRACTED_CRON_DAY_OF_WEEK_VALUE / 100 OR TIME_DAY_OF_WEEK = EXTRACTED_CRON_DAY_OF_WEEK_VALUE % 10 OR (TIME_DAY_OF_WEEK BETWEEN EXTRACTED_CRON_DAY_OF_WEEK_VALUE / 100
                AND EXTRACTED_CRON_DAY_OF_WEEK_VALUE % 10) THEN TRUE
            ELSE FALSE
        END AS RESULT_DAY_OF_WEEK
    FROM TABLE_1
)
SELECT
    TIMEID,
    CRON,
    RESULT_MINUTE,
    RESULT_HOUR,
    RESULT_DAY_OF_MONTH,
    RESULT_MONTH,
    RESULT_DAY_OF_WEEK,
    CASE
        WHEN (RESULT_MINUTE) = TRUE AND (RESULT_HOUR) AND (RESULT_DAY_OF_MONTH) = TRUE AND (RESULT_MONTH) = TRUE AND (RESULT_DAY_OF_WEEK) = TRUE THEN TRUE
        ELSE FALSE
    END AS RESULT
FROM TABLE_2;

希望这段代码对你有所帮助！

- Soham Jagtap