Return to Revisions

This is a perfect use case for PostgreSQL's (fantastic) RANGE type. All of the code below is available on the fiddle here.

An excellent article on them is to be found here (by Dimitri Fontaine) - the main thing to note is:

the main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value.

The key thing to note is that a range is ordered - the first value is less than the second, so no fear of meaningless appointments that start after they finish (and I've seen systems which actually allowed this!).

An interesting post on how they can be used to "turn 100 lines of SQL into 3" can be found here (n.b. multi-range types).

CREATE TABLE test
(
  business_id INT NOT NULL,
  intval      TSTZRANGE,
  EXCLUDE USING GIST (business_id WITH =, intval WITH &&)
);

So, this tells us that no intval can overlap with another with the same business_id (person, resource...).

To test:

INSERT INTO test VALUES
(1, '[2022-01-01 11:30, 2022-01-01 15:00)');

and then:

--
-- Non-overlapping
-- 

INSERT INTO test VALUES
(1, '[2022-01-01 16:30, 2022-01-01 18:00)');

No problem!

But!

--
-- Overlapping!
--

INSERT INTO test VALUES
(1, '[2022-01-01 10:30, 2022-01-01 13:00)');

and we get (correctly):

ERROR: conflicting key value violates exclusion constraint "test_business_id_intval_excl" DETAIL: Key (business_id, intval)=(1, ["2022-01-01 10:30:00+00","2022-01-01 13:00:00+00")) conflicts with existing key (business_id, intval)=(1, ["2022-01-01 11:30:00+00","2022-01-01 15:00:00+00")).

and now with a different business_id, but overlaps with preceding schedules:

--
-- Interval overlaps, but business_id (person, other resource
-- doesn't - no problem!
--

INSERT INTO test VALUES
(2, '[2022-01-01 11:30, 2022-01-01 15:00)');

Resutlt (as expected):

INSERT 0 1

No need to worry about Sundays, Mondays or intervals going over day, week, month or year boundaries. These range types are incredibly powerful and well worth the effort of getting to know. See here for a list of functions and operators.