Cron Expression Parser for PostgreSQL

Introduction

This is an implementation of a cron expression parser for PostgreSQL written in PL/PgSQL.

It provides a function, cronexp.match(), that can be called with a timestamp and a cron expression. The function will return true if the timestamp matches the expression or false otherwise.

Here is an example of a cron expression that would match timestamps at multiples of 5 minutes during night hours:

chris=# select cronexp.match('2021-03-04 00:05:13+00', '*/5 22-23,0-7 * * *');
 match 
-------
 t

Please note this is not a scheduler, it's just a building block for your own scheduling needs.

A possible use case would be a system where you store a job table with a number of cron expressions and related jobs and call this function once a minute to get a list of things that need to be done.

A second function, cronexp.is_wellformed() can be used to check the syntax of a cron expression:

chris=# select cronexp.is_wellformed('*/5 22-23,0-7 * * *');
 is_wellformed 
---------------
 t

Installation

Just execute the file cronexp.sql in your PostgreSQL database. It creates the schema cronexp with two function: cronexp.expand_field() is for internal use and cronexp.match() is the user-facing function.

Optionally, execute the file cronexp_tests.sql to run the tests. When run successfully, the script will raise:

INFO:  198/198 tests passed

The code has been tested on the major PostgreSQL versions 9.5, 9.6, 10, 11, 12, 13 and 14.

Usage

To check whether a timestamp matches a cron expression, call the function cronexp.match(). Its first argument is a timestamp with time zone and its second argument is a cron expression as text. E.g.

chris=# select cronexp.match('2021-09-01 13:20:00+02', '20 13 * * *');
match
-------
t

Here, the timestamp matches the cron expression 20 13 * * * and the function returns true.

Please note the first argument to cronexp.match() is a timestamp with time zone. This means the current time zone influences the match. In the above example the hour must be 13 for the timestamp to match. However, the hour of 2021-09-01 13:20:00+02 is 13 only when evaluated in a time zone that is +02 with respect to GMT.

This is illustrated by the following example:

do $$
    begin
        set local timezone = '+02';
        raise info '%', cronexp.match('2021-09-01 13:20:00+02', '20 13 * * *');
        -- gives true
        set local timezone = '+01';
        raise info '%', cronexp.match('2021-09-01 13:20:00+02', '20 13 * * *');
        -- gives false
    end;
$$ language 'plpgsql';

The second argument is a cron expression. It has five space-separated fields with non-negative integer values (symbolic names are not allowed):

field	values
minute	0-59
hour	0-23
day of month	1-31
month	1-12
day of week	0-7 (0 or 7 is Sunday)

For each field, one or more integer values can be specified using the following syntax:

syntax	meaning
n	matches a given int n
n,m,...	matches any int from the given list of integers
n-m	matches any int in a range from n to and including m
n-m/k	matches any k-th int from the given range, the first is n
*	matches any int from the full range (depending on field)
*/k	matches any k-th int from the full range (depending on field), the first is 1 for day of month or month, or 0 for the other fields

Lists and ranges can be combined with commas, if they contain no wildcard (*), e.g. 1-4,7-23/5 means 1,2,3,4,7,12,17,22.

The function cronexp.match() will throw an exception in case the arguments are not understood. E.g.

chris=# select cronexp.match('2021-09-01 13:20:00+02', '20 13 * *'); -- wrong
ERROR:  invalid parameter "exp": five space-separated fields expected

Author

Chris Mair chris@1006.org

https://www.1006.org

License

This source code is released under the BSD-2-Clause license. See the file LICENSE for details.