Prohibit overlapping intervals in a cyclic (e.g. weekly) schedule

Question

I'm designing a postgres table to record the intervals contained in a weekly schedule. It would hold schedules for multiple businesses, and a simple example dataset might look like:

business_id  interval
-----------  -----------------------------------
1            Sunday   10:00:00 – Sunday 14:00:00
1            Sunday   22:00:00 – Monday 02:00:00
1            Friday   11:00:00 – Friday 16:00:00
1            Saturday 15:00:00 – Sunday 01:00:00

Note that intervals can cross the boundaries between days.

A business should not have overlapping intervals, and I'd like to design the table in a way that lets me enforce this.

I was considering mapping these day-of-week + time-of-day values to the corresponding numbers of seconds since the beginning of the week, storing intervals as int4range and using an exclusion constraint to prohibit overlapping integer ranges, but that wouldn't properly address intervals that wrap around the end of the week.

Is there a good way to model this kind of cyclical data and prohibit overlaps?

Answer 1

Map each day-of-week + time-of-day value to the corresponding number of seconds since the beginning of the week in which the interval started, store the intervals in an int4range column, and add two exclusion constraints.

The first exclusion constraint prohibits intervals that overlap without wrapping around the end of the week:

ALTER TABLE weekly_intervals
ADD CONSTRAINT exclude_overlapping_intervals
EXCLUDE USING GIST (
  business_id WITH =,
  interval WITH &&
)
DEFERRABLE INITIALLY DEFERRED

The second exclusion constraint prohibits intervals that overlap due to one of them wrapping around the end of the week:

-- full week: 7 * 24 * 60 * 60 = 604800

ALTER TABLE weekly_intervals
ADD CONSTRAINT exclude_overlapping_intervals_wraparound
EXCLUDE USING GIST (
  business_id WITH =,
  (
    CASE WHEN upper(interval) > 604800
      THEN int4range(
             lower(interval) - 604800,
             upper(interval) - 604800,
            '[)'
           )
      ELSE interval
    END
  ) WITH &&
)
DEFERRABLE INITIALLY DEFERRED

The exclusion constraints are the interesting part, but for completeness, the table itself would look something like:

CREATE TABLE weekly_intervals (
    business_id bigint NOT NULL,
    interval int4range NOT NULL,
    CONSTRAINT interval_duration_max_one_week CHECK (upper(interval) <= lower(interval) + 604800),
    CONSTRAINT interval_left_closed CHECK (lower_inc(interval)),
    CONSTRAINT interval_right_open CHECK (NOT upper_inc(interval)),
    CONSTRAINT interval_start CHECK (lower(interval) <@ int4range(0, 604800, '[)'))
);

Answer 2

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).

Simple sample table:

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)');

Result (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.

Following comments by the OP, here's some code to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for recurring schedules, as a baseline, it might be of help.

Your comment says (presumably as an example) "Mondays 9AM to 5PM, Tuesdays 10AM to 6PM, .., so I"ve catered for that example - you can obviously add code of your own for more complex scenarios. You could also make use of the range data type as outlined above.

SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL AS "Slot",
  EXTRACT(DOW FROM  '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) AS "Day num",
  TO_CHAR( '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL, 'DAY') AS "Day name",
  CASE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   1 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >=  9 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 17 THEN TRUE
    WHEN 
      EXTRACT(DOW  FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) =   2 AND 
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) >= 10 AND
      EXTRACT(HOUR FROM '2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 DAY'::INTERVAL + h.i * '1 HOUR'::INTERVAL) <= 18 THEN TRUE
    ELSE FALSE
  END AS "Shift"
FROM 
  GENERATE_SERIES(0,  6) AS d(i),  --  <<-- generates hourly slots for weeks/months... on end
  GENERATE_SERIES(0, 23) AS h(i)

LIMIT 150;                        -- don't want to overload db<>fiddle - this shows the example shifts.

Result (snipped for brevity):

i   i   Slot    Day num     Day name    Shift
0   0   2022-10-31 00:00:00     1   MONDAY  f
0   1   2022-10-31 01:00:00     1   MONDAY  f
0   2   2022-10-31 02:00:00     1   MONDAY  f
0   3   2022-10-31 03:00:00     1   MONDAY  f
0   4   2022-10-31 04:00:00     1   MONDAY  f
0   5   2022-10-31 05:00:00     1   MONDAY  f
0   6   2022-10-31 06:00:00     1   MONDAY  f
0   7   2022-10-31 07:00:00     1   MONDAY  f
0   8   2022-10-31 08:00:00     1   MONDAY  f
0   9   2022-10-31 09:00:00     1   MONDAY  t
0   10  2022-10-31 10:00:00     1   MONDAY  t
0   11  2022-10-31 11:00:00     1   MONDAY  t
0   12  2022-10-31 12:00:00     1   MONDAY  t

So, you can see here (and if you examine the fiddle) that Mondays from 09:00 to 17:00 are shift hours and then Tuesdays from 10:00 to 18:00 are shift hours.