This is a perfect use case for PostgreSQL's (fantastic) [`RANGE`][1] type. All of the code below is available on the fiddle [here][2].
An excellent article on them is to be found [here][3] (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][4] (*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][5] for a list of functions and operators.
Following comments by the OP, [here's some code][6] to set up some sort of shift scheduling. It could probably do with some treatment with PL/pgSQL to tidy it up, for 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:
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. I'm not sure that any more help can be forthcoming!
[1]: https://www.postgresql.org/docs/current/rangetypes.html
[2]: https://dbfiddle.uk/gr270OSB
[3]: https://tapoueh.org/blog/2018/04/postgresql-data-types-ranges/
[4]: https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3
[5]: https://www.postgresql.org/docs/15/functions-range.html
[6]: https://dbfiddle.uk/m0xcXdCg