SQL Server Calculate the next run time of CRON expression in SQL

jmo0nnb3  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(117)

I am using CRON UI on my website to create re-occurring schedule for some users task. The UI generates CRON format which is stored in database and i am looking for a way to get the Next run time to know if the task is due and execute that accordingly. Is there a way i can convert the CRON format to get the next run time in SQL?

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

TABLE

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

SQL

  1. Select JobID, CRONSchedule, NextRunTime from TABLE
sql-server

1条答案

按热度按时间
gopyfrb3

gopyfrb31#

I have used this query to determine whether a particular cron is ready to run (Returns t/f). Based on this query you can get the next run date & time, update columns present in the table in the table and can do various such jobs. But this code comes with limitations, we can only enter cron data with a 5-digit structure.

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

This is going to be my table structure

  1. Column |            Type             | Collation | Nullable |               Default                | Storage  | Compression | Stats target | Description 
  2. --------+-----------------------------+-----------+----------+--------------------------------------+----------+-------------+--------------+-------------
  3.  timeid | integer                     |           | not null | nextval('time_timeid_seq'::regclass) | plain    |             |              | 
  4.  time   | timestamp without time zone |           |          |                                      | plain    |             |              | 
  5.  cron   | character varying           |           |          |                                      | extended |             |              |
  1. WITH TABLE_1 AS (
  2.     SELECT
  3.         TIMEID,
  4.         TIME,
  5.         CRON,
  6.         EXTRACT(MINUTE FROM now()) AS TIME_MINUTE,
  7.         EXTRACT(HOUR FROM now()) AS TIME_HOUR,
  8.         EXTRACT(DAY FROM now()) AS TIME_DAY_OF_MONTH,
  9.         EXTRACT(MONTH FROM now()) AS TIME_MONTH,
  10.         EXTRACT(DOW FROM now()) AS TIME_DAY_OF_WEEK,
  11.         (STRING_TO_ARRAY(CRON,' '))[1] AS CRON_MINUTE,
  12.         (STRING_TO_ARRAY(CRON,' '))[2] AS CRON_HOUR,
  13.         (STRING_TO_ARRAY(CRON,' '))[3] AS CRON_DAY_OF_MONTH,
  14.         (STRING_TO_ARRAY(CRON,' '))[4] AS CRON_MONTH,
  15.         (STRING_TO_ARRAY(CRON,' '))[5] AS CRON_DAY_OF_WEEK,
  16.         CASE
  17.             WHEN SPLIT_PART(CRON, ' ', 1) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 1) FROM 3)::INT
  18.             WHEN SPLIT_PART(CRON, ' ', 1) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 1) AS INT)
  19.             WHEN SPLIT_PART(CRON, ' ', 1) = '*' THEN 0
  20.             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
  21.             ELSE NULL
  22.         END AS EXTRACTED_CRON_MINUTE_VALUE,
  23.         CASE
  24.             WHEN SPLIT_PART(CRON, ' ', 2) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 2) FROM 3)::INT
  25.             WHEN SPLIT_PART(CRON, ' ', 2) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 2) AS INT)
  26.             WHEN SPLIT_PART(CRON, ' ', 2) = '*' THEN 0
  27.             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
  28.             ELSE NULL
  29.         END AS EXTRACTED_CRON_HOUR_VALUE,
  30.         CASE
  31.             WHEN SPLIT_PART(CRON, ' ', 3) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 3) FROM 3)::INT
  32.             WHEN SPLIT_PART(CRON, ' ', 3) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 3) AS INT)
  33.             WHEN SPLIT_PART(CRON, ' ', 3) = '*' THEN 0
  34.             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
  35.             ELSE NULL
  36.         END AS EXTRACTED_CRON_DAY_OF_MONTH_VALUE,
  37.         CASE
  38.             WHEN SPLIT_PART(CRON, ' ', 4) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 4) FROM 3)::INT
  39.             WHEN SPLIT_PART(CRON, ' ', 4) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 4) AS INT)
  40.             WHEN SPLIT_PART(CRON, ' ', 4) = '*' THEN 0
  41.             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
  42.             ELSE NULL
  43.         END AS EXTRACTED_CRON_MONTH_VALUE,
  44.         CASE
  45.             WHEN SPLIT_PART(CRON, ' ', 5) LIKE '*/%' THEN SUBSTRING(SPLIT_PART(CRON, ' ', 5) FROM 3)::INT
  46.             WHEN SPLIT_PART(CRON, ' ', 5) ~ '^[0-9]+$' THEN CAST(SPLIT_PART(CRON, ' ', 5) AS INT)
  47.             WHEN SPLIT_PART(CRON, ' ', 5) = '*' THEN 0
  48.             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
  49.             ELSE NULL
  50.         END AS EXTRACTED_CRON_DAY_OF_WEEK_VALUE
  51.     FROM
  52.         TIME
  53. ), TABLE_2 AS (
  54.     SELECT
  55.         TIMEID,
  56.         TIME,
  57.         CRON,
  58.         TIME_MINUTE,
  59.         TIME_HOUR,
  60.         TIME_DAY_OF_MONTH,
  61.         TIME_MONTH,
  62.         TIME_DAY_OF_WEEK,
  63.         EXTRACTED_CRON_MINUTE_VALUE,
  64.         EXTRACTED_CRON_HOUR_VALUE,
  65.         EXTRACTED_CRON_DAY_OF_MONTH_VALUE,
  66.         EXTRACTED_CRON_MONTH_VALUE,
  67.         EXTRACTED_CRON_DAY_OF_WEEK_VALUE,
  68.         CASE
  69.             WHEN CRON_MINUTE LIKE '*/%' AND TIME_MINUTE % EXTRACTED_CRON_MINUTE_VALUE = 0 THEN TRUE
  70.             WHEN CRON_MINUTE ~ '^[0-9]+$' AND TIME_MINUTE - EXTRACTED_CRON_MINUTE_VALUE = 0 THEN TRUE
  71.             WHEN CRON_MINUTE = '*' THEN TRUE
  72.             WHEN CRON_MINUTE ~ '^[0-9]+-[0-9]+$' AND
  73.                 TIME_MINUTE = EXTRACTED_CRON_MINUTE_VALUE / 100 OR TIME_MINUTE = EXTRACTED_CRON_MINUTE_VALUE % 10 OR (TIME_MINUTE BETWEEN EXTRACTED_CRON_MINUTE_VALUE / 100
  74.                 AND EXTRACTED_CRON_MINUTE_VALUE % 10) THEN TRUE
  75.             ELSE FALSE
  76.         END AS RESULT_MINUTE,
  77.         CASE
  78.             WHEN CRON_HOUR LIKE '*/%' AND TIME_HOUR % EXTRACTED_CRON_HOUR_VALUE = 0 THEN TRUE
  79.             WHEN CRON_HOUR ~ '^[0-9]+$' AND TIME_HOUR - EXTRACTED_CRON_HOUR_VALUE = 0 THEN TRUE
  80.             WHEN CRON_HOUR = '*' THEN TRUE
  81.             WHEN CRON_HOUR ~ '^[0-9]+-[0-9]+$' AND
  82.                 TIME_HOUR = EXTRACTED_CRON_HOUR_VALUE / 100 OR TIME_HOUR = EXTRACTED_CRON_HOUR_VALUE % 10 OR (TIME_HOUR BETWEEN EXTRACTED_CRON_HOUR_VALUE / 100
  83.                 AND EXTRACTED_CRON_HOUR_VALUE % 10) THEN TRUE
  84.             ELSE FALSE
  85.         END AS RESULT_HOUR,
  86.         CASE
  87.             WHEN CRON_DAY_OF_MONTH LIKE '*/%' AND TIME_DAY_OF_MONTH % EXTRACTED_CRON_DAY_OF_MONTH_VALUE = 0 THEN TRUE
  88.             WHEN CRON_DAY_OF_MONTH ~ '^[0-9]+$' AND TIME_DAY_OF_MONTH - EXTRACTED_CRON_DAY_OF_MONTH_VALUE = 0 THEN TRUE
  89.             WHEN CRON_DAY_OF_MONTH = '*' THEN TRUE
  90.             WHEN CRON_DAY_OF_MONTH ~ '^[0-9]+-[0-9]+$' AND
  91.                 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
  92.                 AND EXTRACTED_CRON_DAY_OF_MONTH_VALUE % 10) THEN TRUE
  93.             ELSE FALSE
  94.         END AS RESULT_DAY_OF_MONTH,
  95.         CASE
  96.             WHEN CRON_MONTH LIKE '*/%' AND TIME_MONTH % EXTRACTED_CRON_MONTH_VALUE = 0 THEN TRUE
  97.             WHEN CRON_MONTH ~ '^[0-9]+$' AND TIME_MONTH - EXTRACTED_CRON_MONTH_VALUE = 0 THEN TRUE
  98.             WHEN CRON_MONTH = '*' THEN TRUE
  99.             WHEN CRON_MONTH ~ '^[0-9]+-[0-9]+$' AND
  100.                 TIME_MONTH = EXTRACTED_CRON_MONTH_VALUE / 100 OR TIME_MONTH = EXTRACTED_CRON_MONTH_VALUE % 10 OR (TIME_MONTH BETWEEN EXTRACTED_CRON_MONTH_VALUE / 100
  101.                 AND EXTRACTED_CRON_MONTH_VALUE % 10) THEN TRUE
  102.             ELSE FALSE
  103.         END AS RESULT_MONTH,
  104.         CASE
  105.             WHEN CRON_DAY_OF_WEEK LIKE '*/%' AND TIME_DAY_OF_WEEK % EXTRACTED_CRON_DAY_OF_WEEK_VALUE = 0 THEN TRUE
  106.             WHEN CRON_DAY_OF_WEEK ~ '^[0-9]+$' AND TIME_DAY_OF_WEEK - EXTRACTED_CRON_DAY_OF_WEEK_VALUE = 0 THEN TRUE
  107.             WHEN CRON_DAY_OF_WEEK = '*' THEN TRUE
  108.             WHEN CRON_DAY_OF_WEEK ~ '^[0-9]+-[0-9]+$' AND
  109.                 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
  110.                 AND EXTRACTED_CRON_DAY_OF_WEEK_VALUE % 10) THEN TRUE
  111.             ELSE FALSE
  112.         END AS RESULT_DAY_OF_WEEK
  113.     FROM TABLE_1
  114. )
  115. SELECT
  116.     TIMEID,
  117.     CRON,
  118.     RESULT_MINUTE,
  119.     RESULT_HOUR,
  120.     RESULT_DAY_OF_MONTH,
  121.     RESULT_MONTH,
  122.     RESULT_DAY_OF_WEEK,
  123.     CASE
  124.         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
  125.         ELSE FALSE
  126.     END AS RESULT
  127. FROM TABLE_2;

I hope that this code was helpful!!

展开查看全部
赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com