Тип дата в oracle
Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.
Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.
Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения.
Маска
Назначение
Первый день столетия
YEAR, или YYYY, или YY, или Y
Первый день года
Первый день квартала
MONTH, или MON, или MM
Первый день месяца
Тот же день недели, что и первый день текущего года
Тот же день недели, что и первый день текущего месяца
DAY, или DY, или D
Первый день недели
HH, или HH12, или HH24
Всеобщее скоординированное время
Всеобщее скоординированное время, обозначаемое сокращением UTC (Coordinated Universal Time), измеряется с применением высокоточных атомных часов и закладывает основу для мировой системы гражданского времени. Например, все часовые пояса определяются их смещением от UTC. Время UTC измеряется по атомному эталону и периодически регулируется через механизм корректировочных секунд для поддержания синхронизации с временем, определяемым по вращению Земли.
Возможно, вы также знакомы с временем по Гринвичскому меридиану, или GMT (Greenwich Mean Time). Как правило, в практическом контексте это обозначение эквивалентно UTC.
Почему выбрано сокращение UTC, а не CUT? Комитет по стандартизации не мог решить, стоит ли использовать английское сокращение CUT или французское TUC, поэтому сошлись на сокращении UTC, которое не соответствует ни одному языку.
Пример
Рассмотрим несколько примеров функции TO_DATE, чтобы понять, как использовать функцию TO_DATE в Oracle/PLSQL.
Особенности отображения дат в различных приложениях
Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.
Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.
Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:
Проверим, какие параметры для отображения дат использует sqlplus.
Рис. 1. Результат выполнения запроса №25 в sqlplus.
Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.
Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.
Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.
Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.
Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.
Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.
Результаты выполнения запроса представлены на рисунках ниже.
Рис. 4. Результат выполнения запроса №26 в sqlplus.
Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.
Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.
Разница между датами
Если просто отнять от одной даты другую, то мы получим разницу между ними в днях. Также, к датам можно прибавлять и отнимать обычные числа, и Oracle будет оперировать ими как днями:
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.
SYSDATE
Приведение строки к дате
Одна из часто встречающихся ситуаций - необходимость представить строку в виде типа данных DATE . Делается это при помощи функции to_date . Данная функция принимает 2 параметра - строку, содержащую в себе собственно дату, и строку, которая указывает, как нужно интерпретировать первый параметр, т.е. где в этой дате год, где месяц, число и т.п.
На самом деле, функция to_date может работать и без строки с форматом даты, а также с еще одним дополнительным параметром, который будет указывать формат языка, но мы будем рассматривать вариант с двумя параметрами. Более детально ознакомиться с функцией to_date можно вот здесь.
Как видно, строка, определяющая формат даты, имеет очень большое значение. В примере выше, мы получили две разные даты, изменив лишь их формат в функции to_date .
Функция MONTHS_BETWEEN(d1, d2)
Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами d1 и d2 с учетом знака как d1-d2, возвращаемое число является дробным.
SELECT MONTHS_BETWEEN('2.09.2006', '2.05.2006') d1,
MONTHS_BETWEEN('12.09.2006', '2.05.2006') d2,
MONTHS_BETWEEN('2.05.2006', '12.09.2006') d3
D1
D2
D3
Функция SYSDATE
Данная функция возвращает текущую дату. В зависимости от того, когда следующий запрос выполнится, значение SYSDATE будет всегда разным.
Синтаксис
Синтаксис функции Oracle/PLSQL TO_DATE:
Объявление переменных даты и времени в PL/SQL
Синтаксис объявления переменной, представляющей дату и время, выглядит так:
Поле тип заменяется одним из следующих типов:
Значение параметра точность определяет количество десятичных цифр, выделяемое для хранения долей секунды. По умолчанию оно равно 6, то есть время может отслеживаться с точностью до 0,000001 секунды. Допускаются значения от 0 до 9, позволяющие сохранять время суток с высокой точностью.
Функции, возвращающие значения типа TIMESTAMP (например, SYSTIMESTAMP ), всегда возвращают данные с шестью цифрами точности.
Несколько примеров объявлений:
Исходное_значение задается либо при помощи функции преобразования (например, TO_TIMESTAMP ), либо с использованием литерала даты/времени. Оба варианта описаны далее в разделе «Преобразования даты и времени».
Поведение переменной типа TIMESTAMP(0) идентично поведению переменной типа DATE .
Функция to_date и форматы даты
Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.
Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски 'DDD', которая возвращает номер дня в году:
Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.
Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.
Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?
Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида 'DD.MM.YY' или 'DD-MON-YYYY' мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. '2011'.
Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:
В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку '03.02.2011 00:00:00', а запрос №5 — '03.02.2011 00:30:00'.
Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.
Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?
На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.
Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также " и удвоенную одинарную кавычку ''. Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:
Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.
Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:
То выполнение прошло бы успешно, и он вернул строку '09/15/2011 23:00:11'. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка '15.09.11', далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:
Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка '15.09.11' не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.
Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.
- Уровень БД: Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
- Уровень экземпляра: Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
- Уровень сессии: Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса:
Логично предположить, что преобразование строки '11.09.11' в дату пройдет успешно, а строки '11.SEP.11' – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в 'DD-MON-RR'). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.
Попробуем другой пример:
Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка '11.09.11' не удовлетворяет шаблону. Возможно, это мистика?
Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' | 'MON' and 'MONTH' |
'MON | 'MONTH' |
'MONTH' | 'MON' |
'YY' | 'YYYY' |
'RR' | 'RRRR' |
Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:
Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?
Как несложно предположить, в запросе №16 под '11' я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.
В запросе №17 под '99' я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.
Рассмотрим, как работают эти элементы форматирования более детально:
- If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
- If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
- If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
- If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
Поэтому запрос №19 вернет 1950 год в обоих случаях.
Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:
Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.
Какой из запросов не выполнится?
Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.
В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?
Каждый раз, когда сессия с форматом даты по умолчанию 'DD.MON.RR' будет производить вставку с указанием только значения первого столбца будет возникать ошибка.
Выбор типа данных
Естественно, при таком богатстве выбора хочется понять, из каких соображений следует выбирать тип данных для представления даты/времени в той или иной ситуации. В значительной степени выбор типа данных зависит от требуемой детализации:
- Чтобы хранить время с точностью до долей секунды, используйте один из типов 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 . См. далее раздел «Арифметические операции над значениями даты/времени».
В БД Oracle для работы с датами предназначены 2 типа - DATE и TIMESTAMP .
Отдельно можно упомянуть INTERVAL - интервальный тип, который хранит диапазон между двумя датами.
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Маска
Назначение
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'CC') d2
D1
D2
Столетие, причем перед датами до нашей эры ставится знак «минус».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE-1000000, 'SCC') d2
D1
D2
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'YYYY') d2
D1
D2
Аналогично YYYY, но перед датами до нашей эры ставится знак «минус»
Аналогичны YYYY, но возвращаются соответственно последние 3,2 или 1 цифра года.
Oracle/PLSQL функция TO_DATE преобразует строку в дату.
Months_between
Функция months_between возвращает разницу между датами в месяцах:
Функция TRUNC(d[,mask])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
SELECT SYSDATE d1,
D1
D2
Рассмотрим типовые примеры — усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC(SYSDATE, 'HH24') d2,
TRUNC(SYSDATE, 'DD') d3,
TRUNC(SYSDATE, 'MM') d4,
TRUNC(SYSDATE, 'YYYY') d5
D1
D2
D3
D4
D5
Функция TO_CHAR(d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'DD.MM.YY HH24:MI') d2
D1
D2
Функция ROUND(d[,mask])
Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD».
SELECT SYSDATE d1,
ROUND(SYSDATE, 'HH24') d3,
ROUND(SYSDATE, 'DD') d4,
ROUND(SYSDATE, 'MM') d5,
ROUND(SYSDATE, 'YYYY') d6
D1
D2
D3
D4
D5
D6
Функция ADD_MONTHS(d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров x, причем x может быть отрицательным — в этом случае указанное количество месяцев вычитается из заданной даты.
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1,
ADD_MONTHS(SYSDATE, -3) d2
D
D1
D2
SYSTIMESTAMP
Данная функция работает так же, как и SYSDATE , только она возвращает текущую дату в формате TIMESTAMP :
Заключение
В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.
Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.
Большинство приложений выполняют те или иные операции со значениями даты и времени. Работать с датами довольно сложно; кроме того что приходится иметь дело с жестким форматированием данных, существует множество правил определения их допустимых значений и проведения корректных вычислений (приходится учитывать високосные годы, национальные праздники и выходные, диапазоны дат и т. д.). К счастью, СУБД 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 .
Trunc
Функция trunc округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.
Если не указывать формат округления, то trunc округлит до дней, т.е. колонки “2” и “3” будут содержать одинаковое значение.
Тип TIMESTAMP
Тип TIMESTAMP является расширением типа DATE . Он также, как и тип DATE , позволяет хранить год, месяц, день, часы, минуты и секунды. Но пимимо всего этого в TIMESTAMP можно хранить доли секунды.
TIMESTAMP - максимально точный тип данных для хранения даты, точнее в ORACLE уже нет.
При описании колонки с типом TIMESTAMP можно указать точность, с которой будут храниться доли секунды. Это может быть число от 0 до 9. По умолчанию это значение равно 6.
Пример создания таблицы с колонкой типа TIMESTAMP :
Колонка logout_time может хранить доли секунды с точностью до 6 знаков после запятой, а колонка login_time - с точностью до 8 знаков.
Функция TO_DATE(str[,mask [,nls_lang]])
Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр — язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» — она возникает при указании недопустимой форматной маски.
SELECT TO_DATE('12.09.2006') d
D
ADD_MONTHS
Функция add_months добавляет указанное количество месяцев к дате. Для того, чтобы отнять месяцы от даты, нужно передать в качестве второго параметра отрицательное число:
Приведение строки к timestamp
Для приведения строки к типу timestamp используется фукнция TO_TIMESTAMP :
В запросе выше следует обратить внимание на то, как указывается точность долей секунды. ff3 будет сохранять точность до тысячных долей секунды, ff9 - до максимальных 9-и разрядов.
Форматы строк для приведения к датам очень разнообразны. Здесь приведены варианты, которые чаще всего понадобятся на практике. Ознакомиться со всеми форматами строк можно в докумениации.
В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная — время. Это позволяет совершать над датами арифметические операции — сложение и вычитание.
Приведение даты к строке
Чтобы отобразить дату в нужном нам формате, используется функция to_char .
Применение
Функцию TO_DATE можно использовать в следующих версиях Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
EXTRACT
Функция extract позволяет извлечь из даты определенные составные части, например получить только год, или только месяц и т.п.
Извлекаемые части имеют числовой тип данных, т.е. колонки year , month и day всего лишь числа.
Несмотря на то, что тип DATE хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:
В ответ мы получим ошибку ORA-30076: invalid extract field for extract source .
Но если использовать тип TIMESTAMP , то помимо года, месяца и дня с помощью функции EXTRACT можно по отдельности получить значение часов, минут и секунд:
Параметры или аргументы
string1 это строка, которая будет преобразована в дату.
format_mask не является обязательным. Это формат, который будет использоваться для преобразования string1 к дате.
Это может быть одно или сочетание следующих значений:
Параметр | Пояснение |
---|---|
YYYY | 4-х значный год |
YYY YY Y | Последние 3, 2 или 1 цифры(а) года. |
IYY IY I | Последние 3, 2 или 1 цифры(а) года ISO. |
IYYY | 4-значный год в соответствии со стандартом ISO |
RRRR | Принимает год с 2 цифрами и возвращает год с 4 цифрами. Значение между 0-49 вернет 20xx год. Значение между 50-99 вернет 19xx год. |
Q | Квартал года (1, 2, 3, 4; JAN-MAR = 1). |
MM | Месяц (01-12; JAN = 01). |
MON | Сокращенное название месяца. |
MONTH | Название месяца, дополненное пробелами длиной до 9 символов. |
RM | Римская цифра RM (I-XII; JAN = I). |
WW | Неделя года (1-53), где неделя 1 начинается в первый день года и продолжается до седьмого дня года. |
W | Неделя месяца (1-5), где 1-я неделя начинается в первый день месяца и заканчивается седьмым. |
IW | Неделя года (1-52 или 1-53) на основе стандарта ISO. |
D | День недели (1-7). |
DAY | Название дня. |
DD | День месяца (1-31). |
DDD | День года (1-366). |
DY | Сокращенное название дня. |
J | Юлианский день; количество дней с 1 января 4712 г. до н.э. |
HH | Час дня (1-12). |
HH12 | Час дня (1-12). |
HH24 | Час дня (0-23). |
MI | Минута (0-59). |
SS | Секунда (0-59). |
SSSSS | Секунды после полуночи (0-86399). |
FF | Дробные секунды. Используйте значение от 1 до 9 после FF, чтобы указать количество цифр в долях секунды. Например, 'FF4'. |
AM, A.M., PM, или P.M. | Индикатор меридиана. |
AD или A.D | AD индикатор. |
BC или B.C. | BC индикатор. |
TZD | Летнее время информация. Например, 'PST' |
TZH | Часовой пояс час. |
TZM | Часовой пояс минуты. |
TZR | Часовой пояс региона. |
nls_language не является обязательным. NLS language используется для преобразования string1 к дате.
Тип DATE
Тип DATE используется чаще всего, когда необходимо работать с датами в БД Oracle. Он позволяет хранить даты с точностью до секунд.
Некоторые БД, например MySQL, также имеют тип DATE, но там может храниться дата лишь с точностью до дня.
Функция LAST_DAY(d)
Возвращает последнее число месяца, указанного в дате d.
SELECT SYSDATE d,
D
D1
Данная функция очень удобна для определения количества дней в заданном месяце, например:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), 'DD') d1
D
D1
Читайте также: