Postgres time shcedule schema

README.md

Time Schedule SQL schema

A user can't add days, an event entry unless there isn't an overlap in start_time and end_time span.

schedule.sql

CREATE EXTENSION btree_gist;

--CREATE TYPE dow AS ENUM ('MO', 'TU', 'WE', 'TH', 'FR', 'SA', 'SU');
CREATE TABLE day_of_the_week (
    id smallint NOT NULL,
    name varchar NOT NULL,
    UNIQUE (id)
);

INSERT INTO day_of_the_week (id, name) VALUES
    (0, 'Sunday'),
    (1, 'Monday'),
    (2, 'Tuesday'),
    (3, 'Wednesday'),
    (4, 'Thursday'),
    (5, 'Friday'),
    (6, 'Saturday');

CREATE TABLE days (
    id serial NOT NULL,
    user_id integer NOT NULL,
    day smallint NOT NULL REFERENCES day_of_the_week(id),
    start_time time with time zone NOT NULL,
    end_time time with time zone NOT NULL,
    CHECK (start_time < end_time),
    EXCLUDE USING gist (user_id WITH =, day WITH =,
        int4range (
            (EXTRACT (EPOCH from start_time))::integer,
            (EXTRACT (EPOCH from end_time))::integer
        ) WITH &&
    )
);