INTERVAL в PL/SQL: интервал между датами на примере

Типы, рассматривавшиеся до настоящего момента, представляют определенные моменты времени. Типы данных INTERVAL, появившиеся в Oracle9i, предназначены для сохранения и обработки временных промежутков. Чтобы получить более полное представление о них, вспомним, с какими значениями даты/времени мы сталкиваемся в повседневной жизни:

Оглавление статьи[Показать]

Oracle поддерживает два типа данных INTERVAL. Оба типа были введены в Oracle9i, и оба соответствуют стандарту ISO SQL:

Момент — временная точка, определенная с некоторой точностью. Например, когда мы собираемся встать утром в заданное время, это время представляет момент. Он может определяться с точностью до часа, до минуты и т. д. Все типы данных DATE и TIMESTAMP предназначены для определения моментов времени.
Интервал — понятие, которое относится не к конкретной временн?ой точке, а к определенному количеству времени. В повседневной жизни мы постоянно имеем дело с временны? ми интервалами: работаем по восемь часов, обедаем в течение часа (если бы!) и т. д. Для представления интервалов используются два типа данных INTERVAL.
Период — интервал, который начинается и заканчивается в заданный момент. Например, если вы встали в 8:00 и работали в течение восьми часов, то 8-часовой интервал времени, начинающийся в 8:00, можно считать периодом. В Oracle нет специализированного типа данных для непосредственной поддержки периодов, как нет его и в стандарте SQL.

INTERVAL YEAR TO MONTH — позволяет определить интервал времени в годах и месяцах;
INTERVAL DAY TO SECOND — позволяет определить интервал времени в днях, часах, минутах и секундах (с долями секунд).

Объявление интервальных переменных в PL/SQL

По сравнению с другими объявлениями переменных PL/SQL синтаксис объявлений переменных обоих типов INTERVAL несколько необычен. Помимо того, что имена этих типов состоят из нескольких слов, для них задается не одно, а два значения, определяющих точность:

имя_переменной INTERVAL YEAR [(точность_лет)] TO MONTH

или

имя_переменной INTERVAL DAY [(точность_дней)] TO SECOND [(точность_долей_секунды)]

Здесь имя_переменной — имя объявляемой переменной INTERVAL; точность_лет — количество цифр (от 0 до 4), выделенное для представления количества лет (по умолчанию 2); точность_дней — количество цифр (от 0 до 9), выделенное для представления количества дней (по умолчанию 2); точность_долей_секунды — количество цифр (от 0 до 9), выделенное для представления количества долей секунды (по умолчанию 6).

О точности значений интервалов, как правило, можно не беспокоиться. Значения типа INTERVAL YEAR TO MONTH всегда нормализуются таким образом, что количество месяцев лежит в диапазоне от 0 до 11. Фактически Oracle не позволяет задать месяц значением больше 11; интервал в 1 год и 13 месяцев должен быть выражен как 2 года и 1 месяц. Значение параметра точность_лет устанавливает максимальный размер интервала типа INTERVAL YEAR TO MONTH. Аналогичным образом значение параметра точность_дней устанавливает максимальный размер интервала типа INTERVAL DAY TO SECOND.

Точность для часов, минут и секунд для значения типа INTERVAL DAY TO SECOND не нужно задавать по той же причине, по которой не задается точность для месяцев значения типа INTERVAL YEAR TO MONTH. Интервалы INTERVAL DAY TO SECOND всегда нормализуются таким образом, что значения часов, минут и секунд находятся в естественных диапазонах: 0–23 часа, 0–59 минут и 0–59 секунд (за исключением долей секунды).

Доли секунды указываются потому, что значения типа INTERVAL DAY TO SECOND могут определять интервалы с указанной точностью до долей секунды. Значения типа INTERVAL YEAR TO MONTH не могут содержать долей месяца, и последние для них не задаются.

Когда используются типы INTERVAL в PL/SQL

Используйте типы данных INTERVAL во всех случаях, когда вам потребуется обрабатывать промежутки времени. В этом разделе приведены два примера; хочется верить, что они вызовут у вас интерес и помогут представить, как эти типы данных могли бы использоваться в создаваемых вами системах.

Вычисление разности между двумя значениями даты/времени

Типы INTERVAL удобно использовать для вычисления разности между двумя значениями даты/времени. В следующем примере вычисляется срок работы сотрудника:

DECLARE
   start_date TIMESTAMP;
   end_date TIMESTAMP;
   service_interval INTERVAL YEAR TO MONTH;
   years_of_service NUMBER;
   months_of_service NUMBER;
BEGIN
   -- Обычно начальная и конечная даты загружаются из базы данных.
   start_date := TO_TIMESTAMP('29-DEC-1988','dd-mon-yyyy');
   end_date := TO_TIMESTAMP ('26-DEC-1995','dd-mon-yyyy');

   -- Определение и вывод количества отработанных лет и месяцев:
   service_interval := (end_date - start_date) YEAR TO MONTH;
   DBMS_OUTPUT.PUT_LINE(service_interval);

   -- Новая функция EXTRACT выделяет отдельные компоненты года и месяца.
   years_of_service := EXTRACT(YEAR FROM service_interval);
   months_of_service := EXTRACT(MONTH FROM service_interval);
   DBMS_OUTPUT.PUT_LINE(years_of_service || ' years and '
                       || months_of_service || ' months');
END;

Непосредственное вычисление количества лет и месяцев работы выполняется в следующей строке:

service_interval := (end_date - start_date) YEAR TO MONTH;

Здесь YEAR TO MONTH — часть синтаксиса выражения, возвращающего интервал. Подробнее о нем рассказывается далее в этой главе. Как видите, вычисление продолжительности интервала сводится к простому вычитанию одной даты из другой. Без типа данных INTERVAL нам пришлось бы программировать вычисления самостоятельно:

months_of_service := ROUND(months_between(end_date, start_date));
years_of_service := TRUNC(months_of_service/12);
months_of_service := MOD(months_of_service,12);

Решение, в котором не используется тип данных INTERVAL, оказывается более сложным как для программирования, так и для понимания кода.

Тип INTERVAL YEAR TO MONTH выполняет округление значений, и вы должны знать о возможных последствиях этой операции. За подробностями обращайтесь к разделу «Арифметические операции над значениями даты/времени».

Обозначение периода времени

В этом примере анализируется работа конвейерной сборки. Важной метрикой эффективности является время, необходимое для сборки каждого продукта. Сокращение этого интервала повышает эффективность работы конвейера, поэтому начальство желает постоянно контролировать его продолжительность. В нашем примере каждому продукту присваивается контрольный номер, используемый для его идентификации в процессе сборки. Информация хранится в следующей таблице:

TABLE assemblies (
   tracking_id NUMBER NOT NULL,
   start_time TIMESTAMP NOT NULL,
   build_time INTERVAL DAY TO SECOND
);

Также нам понадобится функция PL/SQL, возвращающая время сборки для заданного идентификатора tracking_id. Значение вычисляется вычитанием текущего времени из времени начала сборки. Арифметические операции с датами более подробно рассматриваются позднее в этой главе. Функция вычисления времени сборки:

FUNCTION calc_build_time (
         esn IN assemblies.tracking_id%TYPE
         )
RETURN DSINTERVAL_UNCONSTRAINED
IS
   start_ts assemblies.start_time%TYPE;
BEGIN
   SELECT start_time INTO start_ts FROM assemblies
   WHERE tracking_id = esn;
   RETURN LOCALTIMESTAMP-start_ts;
END;

При передаче интервалов программам PL/SQL и из них необходимо использовать ключевое слово UNCONSTRAINED (см. далее раздел «Типы данных INTERVAL без ограничений»). Хранение времени сборки в таблице упрощает анализ данных. Мы можем легко определить минимальное, максимальное и среднее время сборки при помощи простых функций SQL, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.