Большинство приложений выполняют те или иные операции со значениями даты и времени. Работать с датами довольно сложно; кроме того что приходится иметь дело с жестким форматированием данных, существует множество правил определения их допустимых значений и проведения корректных вычислений (приходится учитывать високосные годы, национальные праздники и выходные, диапазоны дат и т. д.). К счастью, СУБД Oracle и PL/SQL предоставляют набор типов данных для хранения даты и времени в стандартном внутреннем формате.
Для любого значения даты или времени Oracle сохраняет некоторые (или все) из перечисленных составляющих: год, месяц, день, час, минуты, секунды, часовой пояс, смещение часового пояса в часах, смещение часового пояса в минутах.
Впрочем, поддержка типов даты и времени — только часть дела. Еще необходим язык, средства которого позволяют удобно и естественно работать с этими значениями. Oracle обеспечивает разработчиков исчерпывающим набором функций для выполнения все-возможных операций с датами и временем.
В течение долгого времени для работы с датой и временем в Oracle поддерживался только тип DATE. В Oracle9i ситуация немного изменилась: появились три новых типа TIMESTAMP и два новых типа INTERVAL. Они предоставляют много новых полезных возможностей, одновременно улучшая совместимость Oracle со стандартом ISO SQL. Типы данных INTERVAL подробно рассматриваются позднее в моем блоге, а пока остановимся на четырех основных типах даты/времени.
DATE
Хранит значение даты и времени с точностью до секунд. Не содержит информации часового пояса.TIMESTAMP
Хранит значение даты и времени без информации о часовом поясе. Эквивалентен типу данных DATE, отличаясь от него лишь тем, что время хранится с точностью до миллиардной доли секунды.TIMESTAMP WITH TIME ZONE
Хранит вместе со значением даты и времени информацию о часовом поясе с точностью до девяти десятичных позиций.TIMESTAMP WITH LOCAL TIME ZONE
Хранит значение даты и времени с точностью до девяти десятичных позиций. Значения этого типа автоматически преобразуются между часовым поясом базы данных и местным (сеансовым) часовым поясом. При хранении в базе данных значения преобразуются к часовому поясу базы данных, а при выборке они преобразуются к местному (сеансовому) часовому поясу.
Разобраться во всех особенностях этих типов, особенно TIMESTAMP WITH LOCAL TIME ZONE, бывает непросто. Для примера рассмотрим использование типа TIMESTAMP WITH LOCAL TIME ZONE в календарном приложении для пользователей, работающих в разных часовых поясах. В качестве времени базы данных используется всеобщее скоординированное время UTC (Universal Coordinated Time — см. далее врезку «Всеобщее скоординированное время»). Пользователь Джонатан, живущий в Мичигане (Восточный часовой пояс, смещение от UTC составляет –4:00), запланировал проведение видеоконференции с 16:00 до 17:00 в четверг по своему местному времени. У Донны из Денвера (Горный часовой пояс, смещение составляет –6:00) конференция приходится на промежуток времени с 14:00 до 15:00 в четверг. У Селвы из Индии (смещение +5:30) конференция пройдет с 01:30 до 02:30 в пятницу. На рис. 1 показано, как время начала конференции изменяется при выборке из базы данных пользователями из разных часовых поясов.
Рис. 1. Разные типы значений даты-времени
На рис. 1 пользователь Джонатан находится в Восточном часовом поясе с действием летнего времени, в котором время отстает от UTC на 4 часа (UTC-4:00). Джонатан вводит время начала собрания 16:00. Это значение преобразуется к часовому поясу базы данных (UTC) при вставке записи, и в базе данных сохраняется значение 20:00.
Донна находится в Денвере, где также действует летнее время; текущее время отстает от UTC (UTC-6:00). Когда Донна выбирает время начала встречи, значение преобразуется к сеансовому часовому поясу и отображается в формате 14:00. Селва находится в Индии, где летнее время не действует — индийское стандартное время смещено на 5,5 часа вперед от UTC (UTC+5:30). Когда Селва выбирает время начала встречи, значение преобразуется к сеансовому часовому поясу и выводится в формате 1:30.
Поручая преобразования часовых поясов типу данных TIMESTAMP WITH LOCAL TIME ZONE, вы избавляетесь от необходимости программирования сложных правил, связанных с часовыми поясами и летним временем (которое иногда изменяется, как это было в США в 2007 году), а заодно избавляете ваших пользователей от необходимости разбираться с преобразованиями. Правильное время будет предоставляться каждому пользователю просто и элегантно.
В одних случаях база данных должна автоматически изменять формат вывода времени, в других это не нужно. Если вы не хотите, чтобы формат значения времени изменялся в соответствии с сеансовыми настройками, используйте тип данных TIMESTAMP или TIMESTAMP WITH TIME ZONE.
Всеобщее скоординированное время
Всеобщее скоординированное время, обозначаемое сокращением UTC (Coordinated Universal Time), измеряется с применением высокоточных атомных часов и закладывает основу для мировой системы гражданского времени. Например, все часовые пояса определяются их смещением от UTC. Время UTC измеряется по атомному эталону и периодически регулируется через механизм корректировочных секунд для поддержания синхронизации с временем, определяемым по вращению Земли.
Возможно, вы также знакомы с временем по Гринвичскому меридиану, или GMT (Greenwich Mean Time). Как правило, в практическом контексте это обозначение эквивалентно UTC.
Почему выбрано сокращение UTC, а не CUT? Комитет по стандартизации не мог решить, стоит ли использовать английское сокращение CUT или французское TUC, поэтому сошлись на сокращении UTC, которое не соответствует ни одному языку.
За дополнительной информацией о UTC обращайтесь к документации Национального института стандартов и технологий (http://www.nist.gov/pml/general/time/world.cfm) и списку FAQ (http://www.nist.gov/pml/div688/faq.cfm).
Объявление переменных даты и времени в PL/SQL
Синтаксис объявления переменной, представляющей дату и время, выглядит так:
имя_переменной [CONSTANT] тип [{:= | DEFAULT} исходное_значение] Поле тип заменяется одним из следующих типов:
DATE TIMESTAMP [(точность)] TIMESTAMP [(точность)] WITH TIME ZONE TIMESTAMP [(точность)] WITH LOCAL TIME ZONE
Значение параметра точность определяет количество десятичных цифр, выделяемое для хранения долей секунды. По умолчанию оно равно 6, то есть время может отслеживаться с точностью до 0,000001 секунды. Допускаются значения от 0 до 9, позволяющие сохранять время суток с высокой точностью.
Функции, возвращающие значения типа TIMESTAMP (например, SYSTIMESTAMP), всегда возвращают данные с шестью цифрами точности.
Несколько примеров объявлений:
DECLARE
hire_date TIMESTAMP (0) WITH TIME ZONE;
todays_date CONSTANT DATE := SYSDATE;
pay_date TIMESTAMP DEFAULT TO_TIMESTAMP('20050204','YYYYMMDD');
BEGIN
NULL;
END;
/
Исходное_значение задается либо при помощи функции преобразования (например, TO_TIMESTAMP), либо с использованием литерала даты/времени. Оба варианта описаны далее в разделе «Преобразования даты и времени».
Поведение переменной типа TIMESTAMP(0) идентично поведению переменной типа DATE.
Выбор типа данных
Естественно, при таком богатстве выбора хочется понять, из каких соображений следует выбирать тип данных для представления даты/времени в той или иной ситуации. В значительной степени выбор типа данных зависит от требуемой детализации:
- Чтобы хранить время с точностью до долей секунды, используйте один из типов
TIMESTAMP. - Чтобы время автоматически преобразовывалось между часовыми поясами базы данных и сеанса, используйте тип
TIMESTAMP WITH LOCAL TIME ZONE. - Чтобы отслеживать часовой пояс сеанса, в котором были введены данные, используйте тип
TIMESTAMP WITH TIME ZONE. - Вы можете использовать
TIMESTAMPвместоDATE. TIMESTAMPбез долей секунд занимает 7 байт, как и тип данныхDATE. При хранении долей секунд он занимает 11 байт.
Также могут действовать и другие факторы:
- Используйте
DATEв тех ситуациях, в которых необходимо сохранить совместимость с существующим приложением, написанным до появления типов данныхTIMESTAMP. - В общем случае рекомендуется использовать в коде PL/SQL типы данных, соответствующие типам используемых таблиц базы данных (или по крайней мере совместимые с ними). Например, дважды подумайте, прежде чем читать значение
TIMESTAMPиз таблицы в переменнуюDATE, потому что это может привести к потере информации (в данном случае — долей секунд, и возможно, часового пояса). - Если вы работаете с версией старше Oracle9i Database, у вас нет выбора — придется использовать
DATE. - При сложении и вычитании годов и месяцев поведение функции
ADD_MONTHS, работающей со значениями типаDATE, отличается от поведения интервальных арифметических операций с типамиTIMESTAMP. За дополнительной информацией по этому важному, но неочевидному вопросу обращайтесь к разделу «Когда используются типыINTERVAL».
Будьте осторожны при совместном использовании типов данных DATE и TIMESTAMP. Правила арифметических операций для этих типов сильно различаются. Будьте внимательны при использовании традиционных встроенных функций даты Oracle (таких, как ADD_MONTHS или MONTHS_BETWEEN) к значениям типов TIMESTAMP. См. далее раздел «Арифметические операции над значениями даты/времени».
