add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- btree_gist is installed!
SELECT * FROM pg_extension WHERE extname = 'btree_gist';
oid extname extowner extnamespace extrelocatable extversion extconfig extcondition
16389 btree_gist 10 16388 t 1.7 null null 
SELECT 1
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 PARALLEL SAFE 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
CREATE TABLE

CREATE FUNCTION

CREATE FUNCTION
-- 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);

TABLE hoo;
INSERT 0 5

INSERT 0 8

INSERT 0 2
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"] 
SELECT 15
-- 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"] 
SELECT 1