Sample Header Ad - 728x90

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

1 vote
2 answers
394 views
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](https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN) 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?
Asked by ivan (552 rep)
Oct 25, 2022, 03:06 AM
Last activity: Nov 19, 2022, 04:29 PM