В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная - время. Это позволяет совершать над датами арифметические операции - сложение и вычитание.
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.
Функция LAST_DAY(d)
Возвращает последнее число месяца, указанного в дате d.
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
Функция 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
Рассмотрим типовые примеры - усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, "DD") d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "YYYY") d5
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения.
|
Маска |
Назначение |
Первый день столетия |
|
YEAR, или YYYY, или YY, или Y |
Первый день года |
Первый день квартала |
|
MONTH, или MON, или MM |
Первый день месяца |
Тот же день недели, что и первый день текущего года |
|
Тот же день недели, что и первый день текущего месяца |
|
DAY, или DY, или D |
Первый день недели |
HH, или HH12, или HH24 |
|
Функция TO_DATE(str[,mask [,nls_lang]])
Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр - язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» - она возникает при указании недопустимой форматной маски.
SELECT TO_DATE("12.09.2006") d
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Столетие, причем перед датами до нашей эры ставится знак «минус».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE-1000000, "SCC") d2
26.09.2006 17:14:21 |
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "YYYY") d2
Год, записанный прописью c учетом текущего национального языка.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "YEAR") d2
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "MM") d2
Название месяца прописью.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "MONTH") d2
Неделя года.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "WW") d2
День года.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DDD") d2
День недели прописью, при необходимости дополняется до девяти символов пробелами.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DAY") d2
Дата юлианского календаря. Является числом дней от 1.01.4712 до нашей эры.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "J") d2
Час дня по 12-часовой шкале (1-12).
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "HH") d2
Минуты (0-59).
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "MI") d2
Данная форматная маска полезна для измерения временных интервалов в секундах.
Знаки пунктуации. Они выводятся в соответствующие места отформатированной даты.
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DD.MM.YYYY HH24.MI") d2,
TO_CHAR(SYSDATE, "DD/MM/YYYY HH12.MI PM") d3
Элементы форматной маски, предназначенные для вывода текстовой информации, чувствительны к регистру - регистр управляет регистром форматируемого текста. Рассмотрим управление регистром на примере форматной маски DAY:
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, "DAY") d2,
TO_CHAR(SYSDATE, "Day") d3,
TO_CHAR(SYSDATE, "day") d4
26.09.2006 17:47:45 |
Несложно заметить, что если маска записана в верхнем регистре, то и форматируемое текстовое значение возвращается в верхнем регистре. Если в нижнем - форматируемое значение тоже будет в нижнем. И наконец, запись маски с прописной буквы приводит к форматированию формируемого текста таким образом, чтобы он начинался с прописной буквы.
Заключение
В данной статье мы рассмотрели все основные встроенные функции Oracle, предназначенные для работы с датами и выполнения операций преобразования типов с типом «дата». В следующей статье мы рассмотрим функции преобразования и форматирования чисел и перейдем к рассмотрению практических запросов, использующих рассмотренные ранее функции.
Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.
Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится .
Функция to_date и форматы даты
Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number , to_char и to_date . Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы .Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски "DDD", которая возвращает номер дня в году:
--Запрос №1
select to_char(to_date("13.09.2011"),"DDD") "Programmers day" from dual;
Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.
Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.
Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?
--Запрос №2
select to_char(sysdate, "YYYY") from dual;
--Запрос №3
select to_date("03", "DD") from dual;
Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида "DD.MM.YY" или "DD-MON-YYYY" мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. "2011".
Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:
--Запрос №4
select to_char(to_date("03.02.2011","DD.MM.YYYY"),"DD.MM.YYYY HH24:MI:SS") from dual;
--Запрос №5
select to_char(to_date("03.02.2011 30", "DD.MM.YYYY MI"),"DD.MM.YYYY HH24:MI:SS") from dual;
В запросе №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
select to_date("20092011", "YYYYDDMM") from dual;
На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.
Пример №3. Неявная конвертация
--Запрос №7
select months_between("1\сентябрь-9","01$окт/08") from dual;
В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: separator1 separator2
. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также " и удвоенную одинарную кавычку "". Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:
--Запрос №8
select to_date("01сентябрь09") from dual;
--Запрос №9
select to_date("01сен09") from dual;
--Запрос №10 для этого запроса формат даты по умолчанию должен быть DD.MM.RR
select to_date("010909") from dual;
Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.
Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
--Запрос №11
select to_date(sysdate,"mm/dd/yyyy hh24:mi:ss") from dual;
Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:
--Запрос №12
select to_char(sysdate,"mm/dd/yyyy hh24:mi:ss") from dual;
То выполнение прошло бы успешно, и он вернул строку "09/15/2011 23:00:11". Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка "15.09.11", далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:
--Запрос №13
select to_date("15.09.11","mm/dd/yyyy hh24:mi:ss") from dual;
Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка "15.09.11" не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.
Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG .
Существует три уровня, на которых можно задать формат дат:
- Уровень БД: select * from nls_database_parameters where parameter in ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
- Уровень экземпляра: select * from nls_instance_parameters where parameter in ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
- Уровень сессии: select * from nls_session_parameters where parameter in ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса: select SYS_CONTEXT ("USERENV", "NLS_DATE_FORMAT"), SYS_CONTEXT ("USERENV", "NLS_DATE_LANGUAGE") from dual;
Пример №5. Формат DD.MM.RR vs DD-MON-RR
Уделить внимание форматированию дат по умолчанию меня подвигла некая странность в неявном преобразовании строк в дату. Давайте рассмотрим следующий пример:
--Запрос №14 --Устанавливаем формат даты по умолчанию alter session set NLS_DATE_FORMAT="DD.MM.RR"; --Устанавливаем язык даты по умолчанию alter session set NLS_DATE_LANGUAGE="AMERICAN"; --Проверяем значение параметров сессии select * from nls_session_parameters where parameter in ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE"); --Конвертируем строки в дату select to_date("11.09.11") from dual; select to_date("11.SEP.11") from dual;
Логично предположить, что преобразование строки "11.09.11" в дату пройдет успешно, а строки "11.SEP.11" – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в "DD-MON-RR"). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.
Попробуем другой пример:
--Запрос №15
--Устанавливаем формат даты по умолчанию
alter session set NLS_DATE_FORMAT="DD.MON.RR";
--Устанавливаем язык даты по умолчанию
alter session set NLS_DATE_LANGUAGE="AMERICAN";
--Проверяем значение параметров сессии
select * from nls_session_parameters where parameter in ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE");
--Конвертируем строки в дату
select to_date("11.09.11") from dual;
select to_date("11.SEP.11") from dual;
Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка "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 хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:
--Запрос №16
select to_date("11","RR") "RR", to_date("11","YY") "YY" from dual;
--Запрос №17
select to_date("99","RR") "RR", to_date("99","YY") "YY" from dual;
Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №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.
Рассмотрим, как работают эти элементы форматирования более детально:
--Запрос №18
select to_date("00","RR") "00", to_date("49","RR") "49", to_date("50","RR") "50", to_date("99","RR") "99" from dual
union all
select to_date("00","YY") "00", to_date("49","YY") "49", to_date("50","YY") "50", to_date("99","YY") "99" from dual;
Как видно из запроса №18, отличия в работе масок начинаются с 50-х годов, т.е. формат YY всегда возвращает год в текущем столетии, а RR – год <50 воспринимает как год текущего столетия, а год >50 – как год прошлого столетия. На самом деле значения to_date("99","RR") = 1999 и to_date("00","RR") = 2000 верны, только если текущая дата менее 2051 года, после него to_date("99","RR") = 2099 и to_date("00","RR") = 2100. Если у вас есть желание поэкспериментировать – можете сменить время сервера на 2051 год и посмотреть, как изменится результат работы запроса №18, только не делайте этого на работающих системах! В случае изменения времени сервера изменится sysdate и лучше даже не думать о том, какую головную боль вы получите во всех отчетах, таблицах логов и т.д. Общие правила преобразования двухциферного года в четырехциферный с помощью формата RR выглядят так :
If the specified two-digit year is 00 to 49, then
- 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 select to_date("1950","RR") "50RR", to_date("1950","YY") "50YY" from dual;
Поэтому запрос №19 вернет 1950 год в обоих случаях.
Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:
--Запрос №20
select DATE "1928-12-25" from dual;
Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.
Какой из запросов не выполнится?
--Запрос №21
select to_date("1998-ЯНВ-25 17:30","YYYY-MON-DD HH24:MI","NLS_DATE_LANGUAGE=AMERICAN") from dual;
--Запрос №21
select to_date("1998-ЯНВАРЬ-25 17:30","YYYY-MON-DD HH24:MI","NLS_DATE_LANGUAGE=RUSSIAN") from dual;
Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.
В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?
--Запрос №23
create table for_test (a number, b date default to_date("11.09.2011"));
Каждый раз, когда сессия с форматом даты по умолчанию "DD.MON.RR" будет производить вставку с указанием только значения первого столбца insert into for_test(a) values(1);
будет возникать ошибка.
Особенности отображения дат в различных приложениях
Что влияет на отображение датыЭтот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.
Правда заключается в том, что при выполнении запроса
--Запрос №24
select sysdate from dual;
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть
маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.
Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:
--Запрос №25
--Проверяем параметры сессии по умолчанию
select * from nls_session_parameters where parameter in ("NLS_DATE_FORMAT","NLS_DATE_LANGUAGE");
--Выводим дату
select sysdate from dual;
--Меняем параметры сессии по умолчанию
alter session set NLS_DATE_FORMAT="DD.MON.RR hh24:mi:ss";
--Выводим дату
select sysdate from dual;
Проверим, какие параметры для отображения дат использует 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 следующий запрос:
--Запрос №26
--Меняем параметры сессии по умолчанию
alter session set NLS_DATE_FORMAT="DD.MON.RR hh24:mi:ss";
--Выводим дату
select sysdate from dual;
--Пытаемся преобразовать полученные из предыдущего запроса данные в дату
select to_date(ХХХХХХХХ) from dual;
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.
Результаты выполнения запроса представлены на рисунках ниже.
Рис. 4. Результат выполнения запроса №26 в sqlplus.
Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.
Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.
Заключение
В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» - примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.
Oracle реализует набор функций для работы со значениями типа даты/времени . Мы не будем подробно рассматривать все функции, но сводка в табл. 1 познакомит Вас с доступными возможностями. Если какие-то функции вас заинтересуют, обращайтесь за подробным описанием к справочнику Oracle SQL Reference.
Избегайте использования традиционных функций Oracle, обрабатывающих значения типа DATE , при работе с новыми типами данных TIMESTAMP . Вместо них следует по возможности использовать новые функции для типов INTERVAL . А DATE -функции должны использоваться только для обработки значений типа DATE .
Многие из приведенных в табл. 1 функций (в том числе ADD_MONTHS) получают значения типа DATE . При использовании таких функций с новыми типами данных TIMESTAMP могут возникнуть проблемы. Хотя любой из этих функций можно передать значение типа TIMESTAMP , Oracle неявно преобразует его к типу DATE , и только тогда функция выполнит свою задачу, например:
DECLARE ts TIMESTAMP WITH TIME ZONE; BEGIN ts:= SYSTIMESTAMP; --Обратите внимание: в значении переменной ts задаются --дробные секунды И часовой пояс. DBMS_OUTPUT.PUT_LINE(ts); --Изменение значения ts одной из встроенных функций. ts:= LAST_DAY(ts); --Дробные секунды ПОТЕРЯНЫ, а часовой пояс заменен --часовым поясом сеанса. DBMS_OUTPUT.PUT_LINE(ts); END;
Результат:
| Имя | Описание |
| ADD_MONTHS | Возвращает значение DATE , полученное в результате увеличения заданного значения DATE на заданное количество месяцев. См. «Сложение и вычитание интервалов» |
| CAST | Выполняет преобразования между типами данных - например, между DATE и различными значениями TIMESTAMP . См. « CAST и EXTRACT » |
| CURRENT_DATE | Возвращает текущую дату и время в часовом поясе сеанса как значение типа DATE |
| CURRENT_TIMESTAMP | Возвращает текущую дату и время в часовом поясе сеанса как значение типа TIMESTAMP WITH TIME ZONE |
| DBTIMEZONE | Возвращает смещение часового пояса базы данных относительно UTC в форме символьной строки (например, "-05:00"). Часовой пояс базы данных используется только при работе со значениями типа TIMESTAMP WITH LOCAL TIME ZONE |
| EXTRACT | Возвращает значение NUMBER или VARCHAR2 , содержащее конкретный элемент даты/времени - час, год или сокращение часового пояса. См. « CAST и EXTRACT » |
| FROM_TZ | Преобразует TIMESTAMP и данные часового пояса в значение типа TIMESTAMP WITH TIME ZONE |
| LAST_DAY | Возвращает последний день месяца для заданного входного значения DATE |
| LOCALTIMESTAMP | Возвращает текущую дату и время как значение типа TIMESTAMP в локальном часовом поясе |
| MONTHS_ BETWEEN | Возвращает значение NUMBER , содержащее количество месяцев между двумя датами. См. «Вычисление интервала между двумя значениями DATE » |
| NEW_TIME | Преобразует значение типа DATE одного часового пояса в аналогичное значение другого пояса. Функция существует для сохранения совместимости со старым кодом; в новых приложениях следует использовать типы TIMESTAMP WITH TIME ZONE или TIMESTAMP WITH LOCAL TIME ZONE |
| NEXT_DAY | Возвращает дату первого дня недели, следующего за указанной датой |
| NUMTODSINTERVAL | Преобразует заданное количество дней, часов, минут или секунд (на ваш выбор) в значение типа INTERVAL DAY TO SECOND |
| NUMTOYMINTERVAL | Преобразует заданное количество годов и месяцев (на ваш выбор) в значение типа INTERVAL YEAR TO MONTH |
| ROUND | Возвращает значение типа DATE , округленное до заданных единиц |
| SESSIONTIMEZONE | Возвращает смещение часового пояса сеанса (относительно UTC) в форме символьной строки |
| SYS_EXTRACT_UTC | Преобразует значение типа TIMESTAMP WITH TIME ZONE в значение TIMESTAMP с той же датой и временем, нормализованное по времени UTC |
| SYSDATE | Возвращает текущую дату и время сервера Oracle как значение типа DATE |
| SYSTIMESTAMP | Возвращает текущую дату и время сервера Oracle как значение типа TIMESTAMP WITH TIME ZONE |
| TO_CHAR | Преобразует значение даты/времени в символьную строку. См. «Преобразование даты и времени» |
| TO_DATE | Преобразует символьную строку в значение типа DATE . См. «Преобразование даты и времени» |
| TO_DSINTERVAL | Преобразует символьную строку в значение типа INTERVAL DAY TO SECOND . См. «Преобразования интервалов» |
| TO_TIMESTAMP | Преобразует символьную строку в значение типа TIMESTAMP . См. «Преобразование даты и времени» |
| TO_TIMESTAMP_TZ | Преобразует символьную строку в значение типа TIMESTAMP WITH TIME ZONE . См. «Преобразование даты и времени» |
| TO_YMINTERVAL | Преобразует символьную строку в значение типа INTERVAL YEAR TO MONTH . См. «Преобразования интервалов» |
| TRUNC | Возвращает значение типа DATE , усеченное до заданных единиц |
| TZ_OFFSET | Возвращает смещение относительно UTC часового пояса, заданного названием или сокращением, в форме VARCHAR2 (например, "-05:00") |
В этом примере переменная ts содержит значение типа TIMESTAMP WITH TIME ZONE . Это значение неявно преобразуется в DATE при передаче LAST_DAY . Поскольку в типе DATE не сохраняются ни дробные части секунд, ни смещение часового пояса, эти части значения ts попросту отбрасываются. Результат LAST_DAY снова присваивается ts , что приводит к выполнению второго неявного преобразования - на этот раз DATE преобразуется в TIMESTAMP WITH TIME ZONE . Второе преобразование получает часовой пояс сеанса, поэтому в смещении часового пояса в итоговом значении мы видим?05:00.
Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.
1) Функция определения длины строки LENGTH(строка), возвращает количество символов в строке, включая концевые пробелы.
SELECT LENGTH(‘string ’) FROM DUAL вернет значение 7.
2) Функции преобразования регистров символов UPPER(строка), LOWER(строка), INITCAP(строка). Для преобразования символов к верхнему регистру используется функция UPPER().
SELECT UPPER(‘string’) FROM DUAL вернет STRING.
Если необходимо преобразовать символы строки к нижнему регистру используется функция LOWER().
SELECT LOWER(‘STrinG’) FROM DUAL вернет string.
Функция INITCAP преобразовывает каждый первый символ слова к верхнему регистру, а все остальные символы к нижнему при условии, что символ-разделитель между словами пробел.
SELECT INITCAP(‘string1 string2’) FROM DUAL вернет строку String1 String2.
3) Функции для обрезания начальных и концевых пробелов LTRIM(строка), RTRIM(строка), TRIM(строка). Соответственно первая функция обрезает все начальные пробелы строки, вторая – все концевые, а третья все начальные и концевые.
SELECT LTRIM(‘ str1’) FROM DUAL вернет строку str1,
SELECT RTRIM(‘str2 ’) FROM DUAL вернет строку str2,
SELECT TRIM(‘ str3 ’) FROM DUAL вернет строку str3.
4) Функция замены части строки другой строкой REPLACE(исходная_строка, заменяемая_подстрока, заменяющая_подстрока). Для большей ясности рассмотрим пример, в некотором текстовом поле таблицы хранится число. Причем символ-разделитель между целой и дробной частью в некоторых полях «.», а нам для дальнейшей обработки данных нужно, чтобы он во всех полях должен быть «,». Для этого воспользуемся функцией REPLACE следующим образом. REPLACE(field1, ’.’, ’,’) и все символы «.» в поле field будут заменены на символ «,».
SELECT REPLACE(‘My_string’,’_’,’@’) FROM DUAL вернет строку My@string.
5) Функции преобразования данных к другим типам данных. TO_CHAR(число) преобразует число в текст. TO_NUMBER(строка) преобразует текст в число. TO_DATE(строка, формат_даты) преобразует строку в дату определенного формата.
SELECT TO_CHAR(123) FROM DUAL вернет строку 123,
SELECT TO_NUMBER(‘12345’) FROM DUAL вернет число 12345,
SELECT TO_DATE(’01.01.2010’,’dd.mon.yyyy’) FROM DUAL вернет дату 01.JAN.2010.
6) Функция определения вхождения подстроки в строку INSTR(исходная_строка, подстрока, номер_символа). Даная функция позволяет определять номер символа в исходной строке с которого начинается искомая подстрока (если такая есть). Иначе возвращается 0. Например нам нужно определить все должности в таблице Table1, в наименовании которых встречается подстрока «менеджер». Для этого вполне подойдет следующий оператор
SELECT * FROM TABLE1 WHERE INSTR(POST, ‘менеджер’, 1) > 0.
То есть оператор SELECT выведет только те записи из таблицы TABLE1 где искомая подстрока «менеджер» будет найдена. Причем поиск будет осуществляться с первого символа. Если поиск нужно осуществлять с другой позиции, то номер символа для начала поиска указывается в третьем параметре.
SELECT INSTR(‘Small string’, ‘string’, 1) FROM DUAL вернет значение 7,
SELECT INSTR(‘Small string’, ‘String’, 1) FROM DUAL вернет значение 0.
7) Функция выделения в исходной строке подстроки SUBSTR(исходная_строка, номер_начального_символа, количество_символов). Рассмотрим такой пример, в пользовательской таблице хранится адрес в виде наименование населенного пункта, название улицы, номер дома. Причем мы точно знаем, что для наименования населенного пункта отводится строго 20 символов (если наименовании населенного пункта меньше чем 20 символов, то остальная часть заполняется пробелами), для наименования улицы 30 символов, для номера дома 3 символа. Далее нам необходимо перенести все адреса из нашей таблицы в другую и при этом все 3 компонента адреса должны быть в разных полях. Для выделения компонент адреса применим функцию SUBSTR().
SELECT SUBSTR(TABLE_1.ADDRESS, 1, 20) CITY, SUBSTR(TABLE_1.ADDRESS, 21, 30) STREET, SUBSTR(TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1
Конечно для переноса данных необходимо воспользоваться оператором INSERT, но для понимания работы функции SUBSTR вполне подойдет рассмотренный пример.
SELECT SUBSTR(‘My_string’, 4, 3) FROM DUAL вернет строку str.
Рассмотренные выше функции можно использовать во входных параметрах. Так если нам нужно выделить все символы, после какого-то определенного, то в функцию SUBSTR можно передать номер искомого символа из функции INSTR. Например если нужно перенести все символы из поля таблицы, которые расположены после «,» то можно использовать такую конструкцию
SELECT SUBSTR(My_string, INSTR(My_string, ‘,’, 1), LENGTH(My_string)- INSTR(My_string, ‘,’, 1)+1) FROM DUAL.
Для определения начального символа мы вызываем функцию INSTR(), которая вернет номер символа первого вхождения подстрок «,». Далее мы определяем количество символов до конца строки как разницу длины строки и номера первого вхождения подстроки.
8) Для определения кода символа используется функция ASCII(строка), которая возвращает код 1 символа строки. Например
SELECT ASCII(W) FROM DUAL вернет значение 87.
9) Обратная функция преобразования кода символа в символ CHR(число).
SELECT CHR(87) FROM DUAL вернет символ W.
Функции для работы с числами в Oracle.
В СУБД Oracle имеется ряд функций для работы с числами. К ним относятся функции возведение числа в степень POWER(), округление ROUND() и т. д.
1) Функция ABS(число) возвращает абсолютное значение аргумента.
SELECT ABS(-3) FROM DUAL вернет значение 3.
2) Функция CEIL(число) возвращает наименьшее целое, большее или равное переданному параметру.
SELECT CEIL(4.5) FROM DUAL вернет значение 5.
3) Функция FLOOR(число) возвращает наибольшее целое, меньшее или равное переданному параметру.
SELECT FLOOR(3.8) FROM DUAL вернет значение 3.
4) Функция MOD(число_1, число_2) возвращает остаток от деления первого параметра на второй.
SELECT MOD(5, 3) FROM DUAL вернет значение 2. Примечание. Если второй параметр равен 0, то функция возвращает первый параметр.
5) Функция округления ROUND(число_1, число_2). Округляет первый переданный параметр до количества разрядов, переданного во втором параметре. Если второй параметр не указан, то он принимается равным 0, то есть округление производится до целого значения. Примеры
SELECT ROUND(101.34) FROM DUAL вернет значение 101,
SELECT ROUND(100.1268, 2) FROM DUAL вернет значение 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL вернет значение 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL вернет значение -100.12.
6) Функция усечения значения TRUNC(число_1, число_2). Возвращает усеченное значение первого параметра до количества десятичных разрядов, указанного во втором параметре. Примеры
SELECT TRUNC(150.58) FROM DUAL вернет значение 150
SELECT TRUNC(235.4587, 2) FROM DUAL вернет значение 235.45
SELECT TRUNC(101.23, -1) FROM DUAL вернет значение 100
7) В СУБД Oracle имеется ряд тригонометрических функций SIN(число), COS(число), TAN(число) и обратные им ACOS(число), ASIN(число), ATAN(число). Они возвращают значение соответствующей названию тригонометрической функции. Для прямых функции параметром является значение угла в радианах, а для обратных – значение функции. Примеры
SELECT COS(0.5) FROM DUAL вернет значение 0.877582561890373
SELECT SIN(0.5) FROM DUAL вернет значение 0.479425538604203
SELECT TAN(0.5) FROM DUAL вернет значение 0.546302489843791
SELECT ACOS(0.5) FROM DUAL вернет значение 1.0471975511966
SELECT ASIN(0.5) FROM DUAL вернет значение 0.523598775598299
SELECT ATAN(0.5) FROM DUAL вернет значение 0.463647609000806
8) Гиперболические функции. SINH(число),
COSH(число), TANH(число). SINH() возвращает гиперболический синус переданного параметра, COSH() возвращает гиперболический косинус переданного параметра, TANH() возвращает гиперболический тангенс переданного параметра. Примеры
SELECT COSH(0.5) FROM DUAL вернет значение 1.12762596520638
SELECT SINH(0.5) FROM DUAL вернет значение 0.521095305493747
SELECT TANH(0.5) FROM DUAL вернет значение 0.46211715726001
9) Функция возведения в степень POWER(число_1, число_2). Примеры
SELECT POWER(10, 2) FROM DUAL вернет значение 100
SELECT POWER(100, -2) FROM DUAL вернет значение 0.0001
10) Логарифмические функции. LN(число) возвращает натуральный логарифм переданного параметра, LOG(число_1, число_2) возвращает логарифм второго переданного параметра по основанию, переданному первом параметре. Причем первый параметр должен быть больше нуля и не равен 1. Примеры
SELECT LN(5) FROM DUAL вернет значение 1.6094379124341
SELECT LOG(10, 3) FROM DUAL вернет значение 0.477121254719662
11) Функция извлечения квадратного корня SQRT(число). Пример
SELECT SQRT(4) FROM DUAL вернет значение 2.
12) Функция возведение числа е в степень EXP(число). Пример
SELECT EXP(2) FROM DUAL вернет значение 7.38905609893065.
Функции для работы с датами в Oracle
На практике очень часто необходимо анализировать данные в виде дат, производить некоторые операции над ними, изменять формат. Все эти операции уже реализованы в виде встроенных функций. Рассмотрим самые основные из них.
1) ADD_MONTHS(дата, количество_месяцев) возвращает дату, отстоящую от даты, переданной в первом параметре на количество месяцев, указанном во втором параметре. Примеры
SELECT ADD_MONTHS(’01-JAN-2010’, 2) FROM DUAL вернет дату ’01.03.2010’
SELECT ADD_MONTHS(’01-JAN-2010’, -3) FROM DUAL вернет дату ’01.10.2009’
SELECT ADD_MONTHS(’30-JAN-2010’, 1) FROM DUAL вернет дату ’28.02.2010’
2) Для определения текущей даты и времени применяется функция SYSDATE. Область применения данной функции намного шире чем может показаться на первый взгляд. В первую очередь это контроль за вводом данных в БД. Во многих таблицах выделяется отдельное поля для сохранения даты последнего внесения изменений. Также очень удобно контролировать некие входные параметры для отчетов, особенно если они не должны быть больше чем текущая дата. Помимо даты данная функция возвращает еще и время с точностью до секунд. Пример
SELECT SYSDATE FROM DUAL вернет дату ‘22.05.2010 14:51:20’
3) Если необходимо определить последний день месяца, то для этого вполне подойдет функции LAST_DAY(дата). Её можно использовать для определения количества дней, оставшихся в месяце.
SELECT LAST_DAY(SYSDATE) – SYSDATE FROM DUAL.
В результате выполнения данного оператора будет выведено количество дней от текущей даты до конца месяца. Пример
SELECT LAST_DAY(’15-FEB-2010’) FROM DUAL вернет дату ’28.02.2010’.
4) Функция для определения количества месяцев между датами MONTHS_BETWEEN(дата_1, дата_2). Примеры
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’01-JAN-2010’) FROM DUAL вернет значение -6
SELECT MONTHS_BETWEEN(’01-JUL-2009’, ’10-JAN-2010’) FROM DUAL вернет значение -6.29032258064516.
Примечание. Если дни месяцев совпадают, то функция возвращает целое число, в противном случае результат будет дробным, причем количество дней в месяце будет принято 31.
5) Функция NEXT_DAY(дата, день_недели) позволяет определить следующую дату от даты, переданной в первом параметре, которая соответствует дню недели, переданном во втором параметре. Пример
SELECT NEXT_DAY(’01-JUL-2009’, ’mon’) FROM DUAL вернет дату ‘06.07.2009’, то есть следующий понедельник после 1 июля 2009 наступил 6 числа.
6) Округление даты ROUND(дата, формат). Второй параметр не обязателен, если его не указывать, то он принимается за ‘DD’, то есть округление будет произведено до ближайшего дня. Примеры
SELECT ROUND(SYSDATE) FROM DUAL вернет дату ‘23.05.2010’
SELECT ROUND(SYSDATE, MONTH) FROM DUAL вернет дату ‘01.06.2010’, округляется до ближайшего первого дня месяца.
7) Усечение даты. Функция TRUNC(дата, формат). Также как и рассмотренная выше может не иметь второго параметра. В таком случае усечение будет производиться до ближайшего дня. Примеры
SELECT TRUNC(SYSDATE) FROM DUAL вернет дату ’22.05.2010’
SELECT TRUNC(SYSDATE, ‘WW’) FROM DUAL вернет дату ’01.05.2010’
SELECT TRUNC(SYSDATE, ‘Day’) FROM DUAL вернет дату ‘16.05.2010’.
Функции преобразования данных в Oracle
Данный раздел посвящен рассмотрению преобразования данных в различные форматы. На практике довольно распространены ситуации, когда необходимо строковые величины рассматривать как числа и наоборот. Несмотря на небольшое количество функции их возможностей вполне хватает для решения очень сложных прикладных задач.
1) TO_CHAR(данные, формат). На первый взгляд синтаксис довольно прост, но за счет второго параметра можно очень точно описать в какой формат преобразовать данные. Итак в строку можно преобразовать как дату, так и числовое значение. Рассмотрим вариант преобразования даты к строке. Значения самых распространенных форматов приведены в таблице, более полная информация содержится в технической документации.
Таблица значений форматов для преобразования числа в строку.
SELECT TO_CHAR(SYSDATE, ‘D-MONTH-YY’) FROM DUAL вернет строку ‘7-MAY -10’
SELECT TO_CHAR(SYSDATE, ‘DDD-MM-YYYY’) FROM DUAL вернет строку ‘142-05-2010’
SELECT TO_CHAR(SYSDATE, ‘Q-D-MM-YYY’) FROM DUAL вернет строку ‘2-7-05-010’
SELECT TO_CHAR(1050, ‘9.99EEEE) FROM DUAL вернет строку ‘ 1.050E+03’
SELECT TO_CHAR(1400, ‘9999V999’) FROM DUAL вернет строку ‘1400000’
SELECT TO_CHAR(48, ‘RM’) FROM DUAL вернет строку ‘ XLVIII’
2) Функция преобразования строки в дату TO_DATE(строка, формат). Возможные значения форматов уже рассмотрены выше, поэтому приведу несколько примеров использования данной функции. Примеры
SELECT TO_DATE(’01.01.2010’, ‘DD.MM.YYYY’) FROM DUAL вернет дату ‘01.01.2010’
SELECT TO_DATE(’01.JAN.2010’, ‘DD.MON.YYYY’) FROM DUAL вернет дату ‘01.01.2009’
SELECT TO_DATE(’15-01-10’, ‘DD-MM-YY’) FROM DUAL вернет дату ‘15.01.2010’.
3) Функция преобразования строки в числовое значение TO_NUMBER(строка, формат). Самые распространенные значения форматов перечислены в таблице, поэтому рассмотрим применение данной функции на примерах. Примеры
SELECT TO_NUMBER(‘100’) FROM DUAL вернет число 100
SELECT TO_NUMBER(‘0010.01’, ’9999D99’) FROM DUAL вернет число 10.01
SELECT TO_NUMBER("500,000","999G999") FROM DUAL вернет число 500000.