Storing recurring time periods in Oracle database

Question

I'm writing monitoring software, where most of the logic will be in Oracle databasen & pl/sql.

When my monitoring is called it should alert about problems. For example, it should alert about problem if
1. There are less than 2 operation, in every minute, on Friday from 22:00 till 23:00
2. There are less than 5 operation, in every minute, on 31 of January from 22:00-23:00
3. There are less than 3 operation, in every minute, every day from 10:00 till 12:00 If my monitoring is called on 22:30, 31 of January I should compare my operation number to 5.
4. If there are less than 5 operation, in every minute, from Friday 22:00 till Monday 15:00

I was thinking about saving data periods with cron expression format in database. In this case I have to compare SYSDATE (current call date of monitoring function) to cron expression saved in the database.

My questions:
1. How can I find out if SYSDATE falls under cron expression?
2. Is it correct to use cron expressions in this case, at all? Can you suggest any other way of saving periods of time.

Answer 1

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.

Answer 2

It depends on how much flexibility you want. For the examples you provided such structure would be enough:

CREATE TABLE monitoring_periods (
  id INTEGER NOT NULL PRIMARY KEY,
  monit_month VARCHAR2(2),
  monit_day VARCHAR(2),
  monit_day_of_week VARCHAR(3),
  monit_time_from INTERVAL DAY TO SECOND,
  monit_time_to INTERVAL DAY TO SECOND,
  required_ops INTEGER
);

Here are some examples to store the periods and checking against sysdate. I would avoid storing the cron expression literally as a string, as it would require parsing it at query time. However, the more complex your expressions are (kind of '5 4,15,22 */2 * 1-5') the more complicated the structure to store it - you need to think carefully of your requirements.