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.