shiftTable

shiftTable contains shifts with asset, start and finish timestamp

Usage

This table stores shifts

Structure

keydata typedescriptionexample
idserialAuto incrementing id0
typeintegerShift type (1 for shift, 0 for no shift)1
begin_timestamptimestamptzBegin of the shift3
end_timestamptimestamptzEnd of the shift10
asset_idtextAsset ID the shift is performed on (see assetTable)1

DDL

-- Using btree_gist to avoid overlapping shifts
-- Source: https://gist.github.com/fphilipe/0a2a3d50a9f3834683bf
CREATE EXTENSION btree_gist;
CREATE TABLE IF NOT EXISTS shiftTable
(
    id              SERIAL      PRIMARY KEY,
    type            INTEGER,
    begin_timestamp TIMESTAMPTZ NOT NULL,
    end_timestamp   TIMESTAMPTZ,
    asset_id        SERIAL      REFERENCES assetTable (id),
    unique (begin_timestamp, asset_id),
    CHECK (begin_timestamp < end_timestamp),
    EXCLUDE USING gist (asset_id WITH =, tstzrange(begin_timestamp, end_timestamp) WITH &&)
);