Есть ли у меня способ сохранить запись за вторник и среду в одной строке, или у меня должно быть две записи?
Есть несколько способов сохранить несколько временных диапазонов в одной строке. @bma уже предоставила пару из них. Это может быть полезно для экономии места на диске с помощью очень простых временных шаблонов. Чистый, гибкий и нормализованный подход - хранить одну строку на временной диапазон.
Как лучше всего хранить день и время?
Используйте timestamp
(или timestamptz
, если может быть задействовано несколько часовых поясов). Выберите произвольную промежуточную неделю и просто игнорируйте часть даты, используя аспект дня и времени timestamp
. Самый простой и быстрый по моему опыту, и все проверки работоспособности, связанные с датой и временем, встроены автоматически. Я использую диапазон, начинающийся с 1996-01-01 00:00
, для нескольких похожих приложений по двум причинам:
- Первые 7 дней недели совпадают с днем месяца (для
sun = 7
).
- В то же время это самый последний високосный год (29 февраля для годовых моделей).
Тип диапазона
Поскольку вы на самом деле имеете дело с временными диапазонами (а не только с днем и временем), я предлагаю использовать встроенный тип диапазона tsrange
(или tstzrange
). Основное преимущество: вы можете использовать арсенал встроенных функций диапазона и операторы. Требуется Postgres 9.2 или новее.
Например, вы можете создать ограничение исключения, основанное на этом (реализованное внутренне посредством полнофункционального индекса GiST, который может обеспечить дополнительные преимущества), чтобы исключить перекрывающиеся временные диапазоны. Для получения подробной информации рассмотрите этот связанный ответ:
Для этого конкретного ограничения исключения (без перекрывающихся диапазонов на событие) вам необходимо включить в ограничение целочисленный столбец event_id
, поэтому вам необходимо установить дополнительный модуль btree_gist. Установите один раз для каждой базы данных с помощью:
CREATE EXTENSION btree_gist; -- once per db
Или у вас может быть одно простое CHECK
ограничение для ограничения разрешенного периода времени с использованием диапазона, указанного оператором <@
.
Это могло выглядеть так:
CREATE TABLE event (event_id serial PRIMARY KEY, ...);
CREATE TABLE schedule (
event_id integer NOT NULL REFERENCES event(event_id)
ON DELETE CASCADE ON UPDATE CASCADE
, t_range tsrange
, PRIMARY KEY (event_id, t_range)
, CHECK (t_range <@ '[1996-01-01 00:00, 1996-01-09 00:00)') -- restrict period
, EXCLUDE USING gist (event_id WITH =, t_range WITH &&) -- disallow overlap
);
Для еженедельного расписания используйте первые семь дней, пн-вс или как вам удобно. Месячные или годовые расписания аналогичным образом.
Как извлечь день недели, время и т. Д.?
@CDub предоставил модуль для работы с ним на стороне Ruby. Я не могу это комментировать, но в Postgres все тоже можно делать с безупречной производительностью.
SELECT ts::time AS t_time -- get the time (practically no cost)
SELECT EXTRACT(DOW FROM ts) AS dow -- get day of week (very cheap)
Или аналогичным образом для типов диапазонов:
SELECT EXTRACT(DOW FROM lower(t_range)) AS dow_from -- day of week lower bound
, EXTRACT(DOW FROM upper(t_range)) AS dow_to -- same for upper
, lower(t_range)::time AS time_from -- start time
, upper(t_range)::time AS time_to -- end time
FROM schedule;
db ‹› fiddle здесь
Старый sqliddle
ISODOW
вместо DOW
для EXTRACT()
возвращает 7
вместо 0
по воскресеньям. Есть длинный список того, что вы можете извлечь.
Этот связанный ответ демонстрирует, как использовать оператор типа диапазона для вычисления общей продолжительности для временных диапазонов (последняя глава):
11.11.2013