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]
    }'

A diagram demonstrating that each Time/Date Group has multiple Time/Date List entries.