Storing recurring time periods in Oracle database

cronexpression, monitoring, oracle, plsql, time

Solution

Don't do it

I am completely with SpaceTrucker: Don't do it in SQL or PL/SQL, do it in Java with either Java 8 date API or JodaTime.

How to do it nevertheless

But even when you should't do it, there might still be some good reason to do it. So here is how:

Table for each instant you want to check

First let's create a table for each second or minute in the interval you want to check. The granularity and the length of your interval depends on the cron expressions you want to allow. Usually one second for a whole week should be sufficient (about 100'000 rows). If you want to check a whole year, use minutes as granularity (about 500'000 rows). Both amount or rows are nothing for a modern database. On my notebook, according queries return instantly.

CREATE TABLE week AS
SELECT 
  running_second,
  ts, 
  EXTRACT(SECOND FROM ts) as sec,
  EXTRACT(MINUTE FROM ts) as min,
  EXTRACT(HOUR FROM ts) as h,
  to_char(ts, 'Day') as dow
FROM (  
  SELECT 
    level as running_second,
    TO_TIMESTAMP_TZ('2015-09-05 00:00:00 0:00',
     'YYYY-MM-DD HH24:MI:SS TZH:TZM') + 
     NUMTODSINTERVAL(level-1, 'SECOND') AS ts
  FROM dual CONNECT BY level<=60*60*24*7
)
;

Query for each filter expression

Next, you convert each cron expression to a query. You can either use PL/SQL to transform each cron expression to a where clause, or you can use a generic where clause.

You should get something like this:

SELECT
  *
FROM
  week
WHERE
  h  =5
AND min=0
AND sec=0;

or in a generic version:

SELECT
  filter_expression.name, week.ts
FROM
  week, filter_expressions
WHERE
  (fiter_hour is null or h  = filter_hour) 
AND (filter_min is null or min = filer_min)
AND (filter_sec is null or sec = filter_sec);

(given your filters are stored in a table `filter_expressions`, that has a column for each constraint type, and each row has either a parameter for the constraint or `NULL` if the constraint is not applicable).

Store the result in a global temporary table `cron_startpoints`.

Check for violations

Group the table `cron_startpoints` to check for constraint violations. You can count, how many matches are there for Friday or midnight or whatever and can check, whether that number is OK for you or not.

Problem

Original source