Time/Date Groups

Time/date groups define groups of times/dates and/or wildcard patterns that match times/dates:

CREATE TABLE tmdategrp (
    id INTEGER PRIMARY,
    name TEXT UNIQUE,
    description TEXT,
    disabled INTEGER CHECK(disabled BETWEEN 0 AND 1)-- 0=enabled, 1=disabled
);

CREATE TABLE tmdatelist (
    id INTEGER REFERENCES tmdategrp(id),
    tmdate TEXT, -- json format - see below
PRIMARY KEY(id,tmdate)
);

Each time/date group has multiple time/date list entries that specify times/dates, wildcards, or both, that match the submitted command name when matched by the role, and a rewrite column to rewrite the command that is executed. Each individual time/date is specified in JSON format, and can be one of two different formats:

• From/To specific date range: both from and to are specified in epoch seconds:

  '{ "range" : { "from" : 1415851283, "to": 1415887283 }}'

• Day of the week: each day is specified as an array of hours.

  Each hour is a number representing 15 minute intervals defined as a binary mask:

  1 1 1 1
        ^  0 to 14 minutes of the hour
      ^-- 15 to 29 minutes of the hour
    ^---- 30 to 44 minutes of the hour
  ^------ 45 to 59 minutes of the hour
  Therefore the values range from 0 to 15:
      '{
      "mon" : [0,0,0,0,0,0,0,15,15,15,15,15,15,15,15,15,15,15,3,0,0,0,0,0,0],
      "tue" : [0,0,0,0,0,0,0,15,15,15,15,15,15,15,15,15,15,15,3,0,0,0,0,0,0],
      "wed" : [0,0,0,0,0,0,0,15,15,15,15,15,15,15,15,15,15,15,3,0,0,0,0,0,0],
      "thu" : [0,0,0,0,0,0,0,15,15,15,15,15,15,15,15,15,15,15,3,0,0,0,0,0,0],
      "fri" : [0,0,0,0,0,0,0,15,15,15,15,15,15,15,15,15,15,15,3,0,0,0,0,0,0],
      "sat" : [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],
      "sun" : [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
  }'