cron-sql
store cron schedule in sql and use it to filter dates!
The problem
I need to record the meeting schedule of parties we are dealing with into an sql database - can't use any other type of database. I need to represent all the crazy ways user's can define a schedule.
The solution
Cron Expression is a way of representing scheduling information for cron jobs
. Combined with Quartz extensions you get a pretty solid way of representing most if not all of the common ways of scheduling. Armed with that, if we can find a good way of representing and filtering our schedules in the database in this format then we should be good.
Cron-sql
provides a set of functions that allow matching a date to a cron
expression. It consists of the following functions:
cron_isvalueinrange(varchar(10), int)
a function that checks is a value is a member of a cron range e.g. 4-10,1-5 etccron_isvaluemember(varchar(70), int)
a function that checks if a value is a member of a cron list e.g. 1,2,3,4 or 5,6,8,15cron_isbasicmatch(varchar(2000), @value int)
a function that matches the*, - and ,
. This function has a dependency on 2 other functionscron_matchesdayofmonth(varchar(70),datetime)
a function that builds on function (3) above adding support for L(last day of the month) extensioncron_matchesmonth(varchar(70),datetime)
a function that builds on function (3) above and adds support for abbreviated month names i.e.JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
cron_matchesdayofweek(varchar(70,datetime)
a function that builds on function (3) above that adds support for L(last day of the month) and #(nth day of the week) extensions.cron_ismatch(@minute, @hour, @dayofmonth, @month, @dayofweek, @year, @date)
a function that matches a split cron function to a date,cron_split(varchar(2000))
a function that takes a cron expression as string and returns a table with each component part.
an expression * * * * * *
is matched as:
- Minute:
cron_isbasicmatch
- Hour:
cron_isbasicmatch
- Day Of Month:
cron_matchesdayofmonth
- Month:
cron_matchesmonth
- Day Of Week:
cron_matchesdayofweek
- Year:
cron_isbasicmatch
In the db, I don't need to save the expression as a single piece of text to save me from the hustle of splitting the string every time we query.
How is the performance?
Good enough :). Tested against all days for 2100 years since 1800 and did it in 20 seconds however for a year I was done in under 1 second.
If I have a significantly large data set e.g. the dates are narrow but the candidates are a large set then I'll pre-calculate the matches to improve performance of the application.
Queries more often than not will be a table scan; combine with something to narrow the rows that could match.