Basic cron parsing in sql

readme.md

It should parse these ok but nothing more complex

* * * * * - Every minute.

0 0 * * * - At midnight every day.

30 14 * * 5 - At 14:30 (2:30 PM) on every Friday.

0 22 * * 1-5 - At 22:00 (10 PM) from Monday to Friday.

*/5 * * * * - Every 5 minutes.

0 */2 * * * - Every 2 hours.

0 0 1 * * - At midnight on the first day of every month.

0 0 * 1 * - At midnight every day in January.

0 12 15 6 * - At 12:00 (noon) on the 15th of June.

0 9-17 * * * - At the beginning of every hour from 09:00 (9 AM) to 17:00 (5 PM) every day.

15 14 1 * * - At 14:15 (2:15 PM) on the 1st day of every month.

0 22 * * 7 - At 22:00 (10 PM) every Sunday.

*/10 9-17 * * * - Every 10 minutes from 09:00 (9 AM) to 17:00 (5 PM) every day.

0 0 1 1 * - At midnight on January 1st.

45 23 31 12 * - At 23:45 (11:45 PM) on December 31st.

0 0 * * 0 - At midnight every Sunday.

0 0 1-7 * 1 - At midnight on the first Monday of every month.

*/15 14-18 * * 1-5 - Every 15 minutes from 14:00 (2 PM) to 18:00 (6 PM) on weekdays (Monday to Friday).

30 6 */2 * * - At 06:30 (6:30 AM) every 2 days.

0 12 1 */2 * - At 12:00 (noon) on the 1st day of every second month.

0 4 2 1-3 * - At 04:00 (4 AM) on the 2nd day of January, February, and March.

0 2 29 2 * - At 02:00 (2 AM) on February 29th (for leap years).

*/20 * * * 0 - Every 20 minutes on Sundays.

schema.sql

CREATE TABLE `cronjobs` (
  `ID` int(11) NOT NULL,
  `crontab` varchar(50) NOT NULL,
  `command` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `cronjobs` (`ID`, `crontab`, `command`) VALUES
(1, '30 14 * * 5', 'some command');

sql

SELECT *
FROM cronjobs
WHERE (
    (
        SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 1), ' ', -1) = '*' 
        OR SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 1), ' ', -1) = LPAD(MINUTE(NOW()), 2, '0') 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 1), ' ', -1) LIKE '*/%' 
            AND MOD(MINUTE(NOW()), CAST(SUBSTRING(SUBSTRING_INDEX(crontab, ' ', 1), 3) AS UNSIGNED)) = 0)
    )
    AND
    (
        SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 2), ' ', -1) = '*' 
        OR SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 2), ' ', -1) = HOUR(NOW()) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 2), ' ', -1) LIKE '*/%' 
            AND MOD(HOUR(NOW()), CAST(SUBSTRING(SUBSTRING_INDEX(crontab, ' ', 2), 3) AS UNSIGNED)) = 0)
    )
    AND
    (
        SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 3), ' ', -1) = '*' 
        OR SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 3), ' ', -1) = DAY(NOW()) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 3), ' ', -1) LIKE '*/%' 
            AND MOD(DAY(NOW()), CAST(SUBSTRING(SUBSTRING_INDEX(crontab, ' ', 3), 3) AS UNSIGNED)) = 0) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 3), ' ', -1) LIKE '%-%' 
            AND DAY(NOW()) BETWEEN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 3), '-', 1) AS UNSIGNED) 
            AND CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 3), '-', -1) AS UNSIGNED))
    )
    AND
    (
        SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 4), ' ', -1) = '*' 
        OR SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 4), ' ', -1) = MONTH(NOW()) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 4), ' ', -1) LIKE '*/%' 
            AND MOD(MONTH(NOW()), CAST(SUBSTRING(SUBSTRING_INDEX(crontab, ' ', 4), 3) AS UNSIGNED)) = 0) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 4), ' ', -1) LIKE '%-%' 
            AND MONTH(NOW()) BETWEEN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 4), '-', 1) AS UNSIGNED) 
            AND CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 4), '-', -1) AS UNSIGNED))
    )
    AND
    (
        SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 5), ' ', -1) = '*' 
        OR SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 5), ' ', -1) = DAYOFWEEK(NOW()) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 5), ' ', -1) LIKE '*/%' 
            AND MOD(DAYOFWEEK(NOW()), CAST(SUBSTRING(SUBSTRING_INDEX(crontab, ' ', 5), 3) AS UNSIGNED)) = 0) 
        OR (SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 5), ' ', -1) LIKE '%-%' 
            AND DAYOFWEEK(NOW()) BETWEEN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 5), '-', 1) AS UNSIGNED) 
            AND CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(crontab, ' ', 5), '-', -1) AS UNSIGNED))
    )
);