I'm building a cron-as-a-service, to allow users to input their cron expression, and do some stuff periodically.
Here's a simple version of my table:
create table users (
id serial not null primary key,
-- `text` seems the most straightforward data type, any other suggestions?
cron text not null,
constraint valid_cron_expression CHECK (cron ~* '{some_regex}'),
-- snip --
-- maybe some other fields, like what to do on each cron call
)
My backend runs a SQL query every minute. How can I query all the rows whose cron field match the current timestamp (rounded to the closest minute)?
Edit: users input the cron field as a cron expression, e.g. 5 4 * * *.
Edit 2: corrected the fact that cron time resolution is minute, not second.
First of all you don't need to query every second because the cron has only a one minute resolution.
Next, comparing a cron scheduler expression to a timestamp is not a trivial task. I'm not aware of any PostgreSQL module that would be able to parse the cron expressions.
There are two options, either you write your own function to do the comparison, or else you use a external library in the programming language you are using to do the comparison outside of the Database.
Here you will find an example implementation of such a function for Oracle that could easily be ported to PostgreSQL: SQL Query to convert cron expression to date/time format
It is incomplete because it doesn't handle complex expressions like */5 or 5,10,15 for individual fields of the cron expression but this is where I would start.