shiftTable
shiftTable contains shifts with asset, start and finish timestamp
less than a minute
Usage
This table stores shifts
Structure
key | data type | description | example |
---|---|---|---|
id | serial | Auto incrementing id | 0 |
type | integer | Shift type (1 for shift, 0 for no shift) | 1 |
begin_timestamp | timestamptz | Begin of the shift | 3 |
end_timestamp | timestamptz | End of the shift | 10 |
asset_id | text | Asset ID the shift is performed on (see assetTable) | 1 |
Relations
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 &&)
);
Last modified April 17, 2023: build: version 0.9.13 (6cb0a01)