Временные интервалы. Хранение и применение в версии PostgreSQL до 9.2¶
При проектировании баз данных довольно часто приходится использовать структуры для хранения интервалов времени. Интервалы бывают с открытыми границами, когда граница интервала не определена, или с закрытой границей, когда граница имеет конкретное значение. Так же границы интервалов могут принадлежать самому интервалу или не принадлежать ему.
В PostgreSQL 9.2 появился новые типы данных - RANGES. В том числе поддерживаются временные интервалы:
- tsrange - интервал времени (дата + время) без временной зоны
- tstzrange - интервал времени (дата + время) с временной зоной
- daterange - интервал времени (только дата без времени)
Эти типы предлагают некоторые возможности из коробки, но не решают архитектурных задач - как хранить интервалы времени, чтобы их было удобно использовать и поддерживать.
Проблема выбора структуры хранения в общем случае не стоит остро. Создаются два поля begin_date и end_date, которые и описывают границы интервала. Разработчики договариваются принадлежат ли границы интервалов самим интервалам, и определяют, как хранить интервалы с открытыми границами.
Вот так можно описать возможные варианты структуры хранения интервалов:
Круглой скобкой обозначается граница интервала, которая не принадлежит самому интервалу,
граница с квадратной скобкой принадлежит интервалу
(begin_date : end_date) - условие, при котором значение х принадлежит заданному интервалу - begin_date < x < end_date
(begin_date : end_date] - услови - begin_date < x <= end_date
[begin_date : end_date) - условие - begin_date <= x < end_date
[begin_date : end_date] - условие - begin_date <= x <= end_date
Для рассматриваемых интервалов времени проблемы хранения неопределенной границы интервала нет, так как в PostgreSQL есть специальное значение для хранения бесконечного времени - 'infinity' (может быть как положительной так и отрицательной '-infinity').
Пример
['2013-01-01' : 'infinity'] - описывает интервал времени, который начинается 1 январа 2013 года и никогда не заканчивается.
['-infinity' : '2013-01-01') - описывает интервал времени, который заканчивается до 1 января 2013 года.
Как же всё-таки хранить интервалы времени в базе данных? Всё зависит от специфики самой базы данных. Если нужно хранить интервалы времени, границами которых являются даты (без времени), то одним из лучших решением будет следующее.
Временные интервалы рекомендуется хранить в структуре [begin_date: end_date) - дата начала интервала принадлежит самому интервалу и сохраняется как есть, дата окончания интервала не принадлежит самому интервалу - получается, что там реально хранится дата следующего дня - end_date + interval '1 day'.
Почему бы не хранить дату окончания интервала в естественном виде - [begin_date : end_date]?
Предположим, что есть интервал ['2012-01-01' : '2012-12-31'] (весь 2012 год). Проблемы с этим интервалом возникнут, когда понадобится проверить вхождение в интервал значения, которое имеет временную часть.
Если дату 5 января, очень легко проверить на вхождение в интервал
'2012-01-01' <= '2012-01-05' <= '2012-12-31'
то дату + время 9 часов утра 31 декабря, которое тоже должно входить в вышеупомянутый диапазон, проверить немного сложнее
'2012-01-01' <= '2012-12-31 09:00:00' <= '2012-12-31' - это условие уже не выполняется
Вполне логичным решением будет округлять проверяемое число до даты - в этом случае условие будет выполняться. Но тогда при выполнении реального запроса к таблице, индексы работать не будут, и работа приложения может сильно замедлиться.
Хранение же интервала в формате [begin_date : end_date) - не обладает выше описанными недостатками и вся его "сложность" заключается в том, чтобы при использовании интервала увеличить границу его окончания на 1 день.
Примечание
Если же в приложении используются только даты без временной части, то любой рассмотренный вариант будет удобным в использовании и высокопроизводительным.