Неперекрывающиеся, непрерывные диапазоны меток времени (tzzrange) для часов работы

Question

CREATE TABLE operating_period (
  id SERIAL NOT NULL PRIMARY KEY,
  during TSTZRANGE NOT NULL,
  -- other meta fields
);

Требования: 1. Ни один рабочий период не может пересекаться друг с другом.

Вопрос:

1. Как добавить ограничение, чтобы убедиться, что в рабочее время нет совпадений?
2. С точки зрения скорости запросов, мне лучше с двумя столбцами (start_at, end_at) или индекс GIST быстро для tstzrange?
3. В дизайне схемы есть tstzrange обычно используется? Или мне лучше с двумя колонками?

Answer 1

Ответ на 1. ясен. Чтобы убедиться, что нет перекрытия, используйте ограничение исключения:

CREATE TABLE operating_period (
  id serial PRIMARY KEY                -- PK is NOT NULL automatically
 ,during tstzrange NOT NULL
 ,EXCLUDE USING gist (during WITH &&)  -- no overlap
);

Это реализовано с помощью индекса GiST на during, который поддерживает многие типы запросов автоматически. Соответствующий ответ:

• Предотвращение смежных / перекрывающихся записей с помощью EXCLUDE в PostgreSQL
• Выполните запрос часов этой работы в PostgreSQL

Ответы на 2. и 3. не так ясны, потому что это действительно зависит от многих вещей. У обоих есть свои плюсы и минусы. В часы работы я бы, скорее всего, выбрал типы диапазонов в текущих версиях Postgres. Я бы также обеспечил [) границы для всех записей, чтобы все было просто. Подробности в первом связанном ответе.

Если вы должны пойти с (start_at, end_at), вы будете заинтересованы в OVERLAPS оператор:

• Получение результатов между двумя датами в PostgreSQL
• Найти перекрывающиеся диапазоны дат в PostgreSQL

_{В любом случае, руководящим принципом здесь является задать один вопрос на вопрос, а не целый список...}