Типы, рассматривавшиеся до настоящего момента, представляют определенные моменты времени. Типы данных 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, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.