Skip to content

Instantly share code, notes, and snippets.

@andyg2

andyg2/readme.md

Last active July 9, 2024 06:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andyg2/9ad75b8d0ed7b35661a02b8714295b50 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andyg2/9ad75b8d0ed7b35661a02b8714295b50 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. andyg2 revised this gist Jul 9, 2024. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions schema.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,8 @@
    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');
  2. andyg2 created this gist Jul 9, 2024.
    47 changes: 47 additions & 0 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    #### 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.
    47 changes: 47 additions & 0 deletions sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    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))
    )
    );