clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1926612 fiddles created (21865 in the last week).

-- btree_gist is installed! SELECT * FROM pg_extension WHERE extname = 'btree_gist';
oid extname extowner extnamespace extrelocatable extversion extconfig extcondition
16387 btree_gist 10 16384 t 1.5
 hidden batch(es)


CREATE TABLE hoo ( hoo_id serial PRIMARY KEY , shop_id int NOT NULL -- REFERENCES shop(shop_id) -- reference to shop , hours tsrange NOT NULL , CONSTRAINT hoo_no_overlap EXCLUDE USING gist (shop_id with =, hours WITH &&) , CONSTRAINT hoo_bounds_inclusive CHECK (lower_inc(hours) AND upper_inc(hours)) , CONSTRAINT hoo_standard_week CHECK (hours <@ tsrange '[1996-01-01 0:0, 1996-01-08 0:0]') ); -- function to normalize timestamps CREATE OR REPLACE FUNCTION f_hoo_time(timestamptz) RETURNS timestamp LANGUAGE sql IMMUTABLE AS $func$ SELECT timestamp '1996-01-01' + ($1 AT TIME ZONE 'UTC' - date_trunc('week', $1 AT TIME ZONE 'UTC')) $func$; -- function to normalize (and possibly split) ranges CREATE OR REPLACE FUNCTION f_hoo_hours(_from timestamptz, _to timestamptz) RETURNS TABLE (hoo_hours tsrange) LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE COST 500 ROWS 1 AS $func$ DECLARE ts_from timestamp := f_hoo_time(_from); ts_to timestamp := f_hoo_time(_to); BEGIN -- sanity checks (optional) IF _to <= _from THEN RAISE EXCEPTION '%', '_to must be later than _from!'; ELSIF _to > _from + interval '1 week' THEN RAISE EXCEPTION '%', 'Interval cannot span more than a week!'; END IF; IF ts_from > ts_to THEN -- split range at Mon 00:00 RETURN QUERY VALUES (tsrange('1996-01-01', ts_to , '[]')) , (tsrange(ts_from, '1996-01-08', '[]')); ELSE -- simple case: range in standard week hoo_hours := tsrange(ts_from, ts_to, '[]'); RETURN NEXT; END IF; RETURN; END $func$;
 hidden batch(es)


-- INSERT with normalized and split time ranges (where necessary) INSERT INTO hoo(shop_id, hours) VALUES (1, '[1996-01-01 12:00, 1996-01-01 17:00]') , (1, '[1996-01-03 18:30, 1996-01-04 05:00]') -- cross midnight UTC , (1, '[1996-01-04 22:33, 1996-01-06 13:44]') -- multiple days , (1, '[1996-01-07 22:00, 1996-01-08 00:00]') -- cross week: split - 1. ending sunday 24:00 ... , (1, '[1996-01-01 00:00, 1996-01-01 10:00]') -- .. 2. starting mon 00:00 .. ; -- INSERT with *any* timestamps, processed with f_hoo_hours() -- You can really use *any* timestamptz, they are normalized automatically INSERT INTO hoo(shop_id, hours) SELECT id, f_hoo_hours(f, t) FROM ( VALUES (2, timestamptz '1996-01-01 12:00+00', timestamptz '1996-01-01 17:00+00') , (2, '1996-01-07 22:00+00', '1996-01-08 10:00+00') -- cross week: split in 2 rows !!! , (3, '2016-01-11 00:00+04', '2016-01-11 08:00+04') , (4, '2016-01-11 00:00-04', '2016-01-11 08:00-04') , (5, '2016-01-11 02:00+04', '2016-01-11 08:00+04') ) t(id, f, t); -- Even works with timestamp without time zone: time zone of current session is assumed INSERT INTO hoo(shop_id, hours) SELECT id, f_hoo_hours(f, t) FROM ( VALUES (6, timestamp '2016-01-11 00:00', timestamp '2016-01-11 08:00') , (7, '2016-01-11 00:00', '2016-01-11 08:00') ) t(id, f, t);
5 rows affected
8 rows affected
2 rows affected
 hidden batch(es)


TABLE hoo;
hoo_id shop_id hours
1 1 ["1996-01-01 12:00:00","1996-01-01 17:00:00"]
2 1 ["1996-01-03 18:30:00","1996-01-04 05:00:00"]
3 1 ["1996-01-04 22:33:00","1996-01-06 13:44:00"]
4 1 ["1996-01-07 22:00:00","1996-01-08 00:00:00"]
5 1 ["1996-01-01 00:00:00","1996-01-01 10:00:00"]
6 2 ["1996-01-01 12:00:00","1996-01-01 17:00:00"]
7 2 ["1996-01-01 00:00:00","1996-01-01 10:00:00"]
8 2 ["1996-01-07 22:00:00","1996-01-08 00:00:00"]
9 3 ["1996-01-01 00:00:00","1996-01-01 04:00:00"]
10 3 ["1996-01-07 20:00:00","1996-01-08 00:00:00"]
11 4 ["1996-01-01 04:00:00","1996-01-01 12:00:00"]
12 5 ["1996-01-01 00:00:00","1996-01-01 04:00:00"]
13 5 ["1996-01-07 22:00:00","1996-01-08 00:00:00"]
14 6 ["1996-01-01 00:00:00","1996-01-01 08:00:00"]
15 7 ["1996-01-01 00:00:00","1996-01-01 08:00:00"]
 hidden batch(es)


-- QUERY SELECT * FROM hoo WHERE hours @> f_hoo_time('2014-03-01 08:27+01');
hoo_id shop_id hours
3 1 ["1996-01-04 22:33:00","1996-01-06 13:44:00"]
 hidden batch(es)