Oracle timestamp 6 формат
Обновлено: 05.07.2024
При отображении информации о времени или датах в Oracle критично, какую версию сервера вы используете. Например, начиная с версии 9, стало возможным представление моментов времени и временных интервалов используя типы данных ANSI SQL. Например, timestamp and interval. Ранние версии использовали тип данных date, с точностью одна секунда и временными интервалами как числа (где 1=один день).
Если вы используете новые версии сервера Oracle, то настоятельно рекомендуется использовать новые типы данных в ваших приложениях, если конечно не планируется использовать приложение на серверах не поддерживающих такие типы.
Выборка по дате
Для иллюстрации используем простой пример регистрации пользователей в системе, например на каком либо форуме. Для этого создадим таблицу и наполним ее данными:
Теперь выберем пользователей, которые зарегистрировались в течение последнего дня:
или, в зависимости от национальных настроек
Для явного задания формата вывода даты, перепишем предложение так:
Кроме того, по своему желанию, вы можете изменять интервал, увеличивая или уменьшая его, в зависимости от задачи. Например:
Вы можете явно указывать формат для даты и времени:
SELECT EMAIL, TO_CHAR(REGISTRATION_DATE,'Day, Month DD, YYYY') AS REG_DAY
FROM USERS
ORDER BY REGISTRATION_DATE;
В результате видим, мягко говоря, неудобоваримое форматирование. Исправляем ситуацию, переписав SQL предложение так:
Подводные камни
Некоторые вещи следует знать, и для простоты покажу на примерах:
Маленькие неприятности
Добавим еще несколько строк к нашей таблице со списком пользователей:
Например, нам интересна средняя продолжительность времени между регистрацией пользователей:
Перепишем запрос с объединением:
Для обеспечения уникальности используем колонку USER_ID, поскольку время и дата регистрации не обеспечивают уникальность, т.к. возможна ситуация одновременной регистрации двух пользователей.
Сейчас у нас имеется рядом расположенные колонки с данными, представляющие собой некий отчет, и можно подсчитать интервалы:
Интервал для каждой колонки возвращает день, часы, минуты и секунды. По этим временным точкам можно рассчитать средний интервал. Для этого напишем такой запрос:
Oracle выдал ошибку. В чем же дело? К сожалению Oracle не оказался достаточно умным, чтобы рассчитать среднее значение временного интервала. И как бы ни было печально, рассчитать среднее значение оказалось не так просто как хотелось бы.
Если у вас есть желание, то вы можете переписать предложение SQL в таком виде:
Если вы хотите использовать такие дикие запросы, то пожалуйста, но в данном случае целесообразнее создать представление:
До Oracle 9i, только в одном типе данных (DATE) разрешалось хранить point-in-time значения (общий термин, который включает в себя информацию о дате и времени), даже сейчас Oracle не имеет независимых типов данных только для даты или только для времени, как в некоторых других языках. Хотя в Oracle уже добавили другие типы данных связанных со временем, DATE до сих пор считается наиболее удобным и простым в этой группе.
Тип данных DATE может содержать следующую информацию: век, год, месяц, день, час, минута, и секунда. Правильными считаются даты в диапазоне Январь 1, 4712 BC to Декабрь 31, AD 9999. Используйте следующий код для объявления переменных типа данных DATE:
Обявление переменной типа DATE очень простая задача. Вам не надо использовать параметры.
Однако для того чтобы взять часть информации из DATE-переменной вам необходимо будет воспользоваться функцией TO_CHAR с форматом даты показанном в листинге:
/02/12/2006
13:06
12-FEB-06
Применение масок форматов
Для использования даты в строковом формате, необходимо воспользоваться функцией TO_CHAR,например, так:
Функция TO_CHAR конвертирует дату в строку используя специальный формат маски. Таблица. Наиболее часто используемые опции форматирования.
Вы можете использовать некоторые символы в качестве разделителей (например, запятая, точка, пробел, и точка с запятой) с маской формата, но все остальное необходимо заключать в двойные кавычке, например так:
Today is: SUNDAY
Примечание:
5 — Вы можете использовать двоеточие (:) без кавычек, но строка «Сегодня» должна быть в кавычках. Вы можете обернуть процесс, используя функцию TO_DATE для конвертирования строковых значений в значения даты используя надлежащие форматы, например:
Формат DATE позволяет хранить любые временные данные от веков до секунд. Вам необходимо лишь указать желаемые единицы измерения.
Например:
2006-02-01 19:40:00
2006-02-11 00:00:00
Значения по умолчанию:
Oracle использует следующие правила для установки значения по умолчанию:
5 — Пропущенная дата по умолчанию является первым днем текущего месяца.
8 — Пропущенное время приравнивается к полночи текущего дня. Остальные правила:
- Пропущенный год или месяц по умолчанию являются текущими.
- Пропущенный день по умолчанию является первым днем месяца (если иной текущий месяц не указан).
- Пропущенные часы, минуты, секунды по-умолчанию приравниваются к нулевому значению.
Проверка формата данных
Примечания:
6 — Нет двойных кавычек вокруг строки “Today is” в маске формата, но процедура успешно создалась.
15 — Если вы попробуете запустить процедуру P_FORMAT, Oracle выдаст вам ошибку.
Проверяйте правильность при задании формата данных. Значительно проще проверять код на правильность на этапе разработки, нежели искать ошибки во время его работы.
Использование TIMESTAMP
Тип данных DATE имеет ряд ограничений. Поэтому Oracle ввел тип данных TIMESTAMP, который мы и рассмотрим.
Например, с помощью типа данных TIMESTAMP вы можете определить доли секунды (в DATE точность ограничивается секундами).
Повышенная точность определения времени может быть полезна для работы с событиями происходящими в одно и то же время с точностью до секунды.
По умолчанию, Oracle хранит 6 точных цифр, но вы можете указать точность в диапазоне от 0 до 9. TIMESTAMP (0) равен DATE. В примере показано, как объявить TIMESTAMP тип данных:
2 — Для инициализации переменной необходимо использовать SYSTIMESTAMP, а не SYSDATE.
5 — новый элемент маски формата данных FF8 предназначен для задания долей секунд. Если вы укажете точность меньше той что хранится в БД, Oracle использует технику округления результата выдачи идентичную округлению для чисел с плавающей точкой.
Использование TIMESTAMP с параметром TIMEZONE
Еще одно ограничение типа данных DATE заключается в том, что даже если у вас есть значение типа DATE, хранить часовой пояс, в котором
оно была создана невозможно.
Но вы можете хранить часовой пояс для TIMESTAMP данных используя параметр TIMEZONE, как показано в примере:
Oracle может определить time zone используя информацию с сервера БД либо с клиентского компьютера.
Вы можете сами посмотреть эти значения, используя встроенные функции DBTIMEZONE and SESSIONTIMEZONE, как показано в примере:
17:50:42.828000 -05:00-05 00
2-3 — Встроенная функция CURRENT_TIMESTAMP поддерживает тип данных TIMESTAMP в сессионной (клиентской) time zone, но не в time zone базы данных.
6 — Формат TZR возвращает информацию о time zone региона.
В зависисмости от настроек БД, он может из себя представлять или разницу в часах и минутах между сессионной time zone и UTC (всемирное время, ранее время по Гринвичу) или название региона.
8 — Если вы хотите вернуть только разницу во времени в часах и минутах можно воспользоваться параметрами TZH и TZM.
12 — На этой строке мы видим результат работы скипта. Для компьютера, находящегося в Западном Стандартном часовом поясе, разница
во времени составляет 5 часов.
Если ваша система работает в нескольких часовых поясах, информация полученная из CURRENT_TIMESTAMP позволяет точнее определить время активности клиентов вашей БД.
В противном случае (не используя CURRENT_TIMESTAMP), вы не сможете дифференцировать время между 3 ч. дня Восточнго полушария и 3 ч. дня для временного пояса Тихого океана.
Хранение прошедшего времени с помощью типа данных INTERVAL
INTERVAL — еще один полезный тип данных введеный в Oracle 9i для того чтобы расширить функциональность типа данных DATE. Очень часто, вам не нужно хранить точку во времени, но время, прошедшее между точками, то есть интервал. Например, если вы хотите поддерживать услуги телефонной связи, вам необходимп сохранить день, когда был принят вызов и продолжительности звонка, но вам не нужно сохранять информацию о точном времени начала и окончания каждого вызова.
Единственная информация, которую вам нужна в данном случае — сколько времени человек потратил на вызов. Конечно, вы можете использовать две переменные (начало и конец дата/время) и некоторые другие способы, например, хранение часов, минут и секунд в независимых числовых столбцах. Но в как альтернатива, вы можете хранить интервал независимо, который можно будет самостоятельно обрабатывать позднее, как показано здесь:
INTERVAL содержит два типа данных которые позволят вам установить соответствующие более точные значения интервалов:
Тип данных INTERVAL YEAR TO MONTH позволяет хранить и манипулировать интервалами типа годов и месяцев. Вы можете указать количество символов для хранения годов которые вы хотите хранить (по умолчанию 2, доступный диапазон 0–4). Тип данных INTERVAL DAY TO SECOND позволяет хранить и манипулировать интервалами типа дней, часов, минут и секунд. В этом случае, точность для дней позволяет вам ввести число символов для хранения, и точность для секунд определяет число символов для хранения долей секунд.
Работа с датами встроенных функций
Oracle предоставляет ряд встроенных функций для работы с типом данных DATE. Мы обсудим наиболее часто используемые из них ниже.
Функция EXTRACT
Функция EXTRACT позволяет выделить определенную часть даты/интервала/времени (только год, только месяц, и так далее до секунд) как показано на примере:
Функция EXTRACT всегда возвращает численное значение, таким образом если вам необходимо вернуть текстовое наименование месяца то необходимо воспользоваться
функция TO_CHAR. Но если вам необходимо выделить месяц из значения даты, функция EXTRACT быстрее и эффективнее TO_CHAR. Простой пример:
В функцию EXTRACT можно передавать следующие параметры YEAR, MONTH, DAY, HOUR, MINUTE, и SECOND. Так же вы можете использовать типы данных TIME ZONE и TIMESTAMP.
Функции TRUNC and ROUND
Встроенные функции TRUNC и ROUND, обычно применяемые к числовым значениям, так же работают с датами и временем так же как с числами. Функция TRUNC усекает дату до определенного уровня точности, в тоже время ROUND округляет дату до определенной единицы:
Пример использования функций TRUNC и ROUND:
2006-02-12 00:00:00
2006-01-01 00:00:00
2006-02-12 19:00:00
Есть одна важная деталь при сипользовании дат: если точное время не имеет значения и вы хотите использовать запросы с сортировкой по конкртеной дате, всегда усекайте их.
Разумной альтернативой является построение function-based index (see the Oracle manuals) на усекаемое значение TRUNC(value). Это даст вам хороший механизм повышения производительности для поисковых запросов где время не так уж и важно, но вы все еще хотите сэкономить его для других запросов.
Функция ADD_MONTHS
Так как длина месяцев может различаться работать с точностями более чем день не так уж и просто. 29 дней могут быть более некоторого месяца, меньше, или равно. Oracle делает вашу жизнь проще предоставляя встроенные функции для работы с месяцами: ADD_MONTHS, MONTHS_BETWEEN, и LAST_DAY. Функция ADD_MONTHS добавляет целое месяцев к определенной дате.
v_dt:= ADD_MONTHS(date,integer);
6 — Этот код добавляет один месяц к текущему дню.
8 — Этот код добавляет один месяц к Январь 30, 2003.
12 — Результатом этой операции является, дата с текущим временем, но на один месяц в прошлое.
13 —
Вторая операция значительно более интересна так как вы добавляете месяц к дате которая не существует (Ведь не существует 30-го февраля). В этом случае Oracle возвращает полночь следующего дня результирующего месяца. Это поведение самая важная причина использования функции ADD_MONTHS. В противном случае, если вы просто добавите 30 дней, вы можете получить дату в Марте.
Функция LAST_DAY
Функция LAST_DAY возвращает последний день месяца от определенной даты.
v_dt:= LAST_DAY (date);
10 — Помните что каждый раз получая последний день месяца время сохраняется.
Не забудьте усечь дату если вам необходимо получить полночь последнего дня месяца.
Функция MONTHS_BETWEEN
v_nr:= MONTHS_BETWEEN(date1,date2);
Эта функция возвращает число месяцев между двумя датами. Если разница не является точной (целочисленной) вы получите число с
плавающей запятой где десятичная часть представляет остаток от деления N/31 где N число оставшихся дней. Если SYSDATE в районе начала Февраля, вы получите примерно такой результат:
Так как число месяцев представлено числом с плавающей точкой вы всегда можете представить рисунок из дней, часов, минкт, и даже секунд, но технически это делать не рекомендуется. Используйте вместо этого тип данных интервал INTERVAL. Как правило резульатом этой функции является либо целое либо при необходимости усеченное число.
Запись опубликована 13.01.2010 в 5:44 дп и размещена в рубрике Oracle PL/SQL для чайников. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.
Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.
Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.
Функция 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' будет производить вставку с указанием только значения первого столбца будет возникать ошибка.
Особенности отображения дат в различных приложениях
Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.
Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается 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’ом были приведены для отображения в свой формат, отличный от формата сессии.
Заключение
В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.
Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.
По умолчанию Oracle выводит даты в формате DD-MON-YY, где YY - две последние цифры года:
select sysdate from dual;
При вставке в таблицу значений типа date, по умолчанию можно использовать литерал в формате
DD-MON-YYYY
(две цифры номера дня, три буквы месяца и четыре цифры года)
или использовать ключевое слово DATE для передачи в базу литерала типа data в формате ANSI
YYYY-MM-DD
(четыре цифры года, две цифры месяца, две цифры номера дня)
insert into t1 (d) values ( DATE '1971-04-28');
Конвертация даты в строку:
select to_char(sysdate) from dual;
select to_char(sysdate, 'DD') from dual; -- день
select to_char(sysdate, 'MONTH') from dual; --месяц
select to_char(sysdate, 'YYYY') from dual; -- год
select to_char(sysdate, 'HH24:MI:SS') from dual; -- часы, минуты, секунды
select to_char(sysdate, 'DD MONTH YYYY HH24:MI:SS') from dual; -- комбинация параметров формата
02 ИЮЛЬ 2014 17:00:51
select to_char(sysdate, 'CC') from dual; -- двузначное столетие (век)
select to_char(sysdate - 1000000, 'SCC') from dual; -- двузначное столетие (век), со знаком минус до нашей эры
select to_char(sysdate, 'Q') from dual; -- однозначный квартал года
Немного о стандарте ISO.
В стандарте ISO, год, относящийся к номеру недели ISO, может отличаться от календарного года.
1 января 1988 года попадает на 53-ю неделю ISO для 1987 года.
Неделя всегда начинается с понедельника и заканчивается воскресеньем.
Как связан год с номером недели по стандарту ISO:
Если 1 января падает на пятницу, субботу или воскресенье, то неделя, включающая 1 января,
считается последней неделей предыдущего года, потому что большинство дней этой недели
принадлежат предыдущему году.
Если 1 января падает на понедельник, вторник, среду или четверг, то эта неделя считается
первой неделей нового года, потому что большинство дней этой недели принадлежат новому году.
1 января 1991 падает на вторник, поэтому неделя с понедельника, 31 декабря 1990 по воскресенье, 6 января 1991 считается неделей 1.
Чтобы получить номер недели ISO, используйте маску формата 'IW' для номера недели и одну из масок вида 'IY' для года.
select to_char( DATE '1991-01-01', 'YYYY WW') from dual; -- в обычном календарном формате
select to_char( DATE '1991-01-01', 'IYYY IW') from dual; -- в формате по ISO
в данном случае результаты совпадают.
Попробуем с другой датой:
Как видим результаты разные.
При вставке в таблицу даты, рекомендуется указывать все четыре цифры года.
Если указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет интерпретировать в зависимости от того, какой формат был использован при вводе.
Если использовать формат YY, то в качестве столетия будет использовано текущее столетие,
которое в настоящее время установлено на сервере.
Неважно какой год мы указали, столетие всегда будет текущее (т.е. 20)
Если использовать формат YYYY но при этом указать только две последние цифры года
то в качестве столетия Oracle подставит нули (т.е. 00)
Если использовать формат RR и указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет вычислять по следующим правилам:
Если указанный год находится в интервале от 00 до 49 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 50 до 99,
то столетие при этом будет увеличено на 1 (текущее столетие + 1).
Если указанный год находится в интервале от 50 до 99 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 00 до 49,
то столетие при этом будет уменьшено на 1 (текущее столетие - 1).
Вобщем запомнить легко, если указанный год, больше текущего диапазона, значит столетие уменьшаем
и наоборот если указанный год, меньше текущего диапазона, значит столетие увеличиваем.
Интересно, а что будет если использовать формат RRRR, но при этом указать только две последние цифры года:
В качестве столетия Oracle не подставил нули, вывод аналогичен формату RR.
Для выделения первой цифры столетия в формате года можно использовать запятую:
select to_char(sysdate, 'Y,YYY') from dual; -- год с разделителем
Допустимые форматы года:
select to_char(sysdate, 'YYYY IYYY RRRR SYYYY Y,YYY YYY IYY YY IY RR Y I') from dual; -- год в различных форматах
2014 2014 2014 2014 2 014 014 014 14 14 14 4 4
А также год прописью:
select to_char(sysdate, 'YEAR') from dual; -- в верхнем регистре
select to_char(sysdate, 'Year') from dual; -- каждое слово с большой буквы
Форматы месяца:
select to_char(sysdate, 'MM') from dual; -- двузначный номер месяца
select to_char(sysdate, 'MONTH') from dual; -- полное название в верхнем регистре
select to_char(sysdate, 'Month') from dual; -- полное название с большой буквы
select to_char(sysdate, 'MON') from dual; -- три первые буквы в верхнем регистре
select to_char(sysdate, 'Mon') from dual; -- три первые буквы с большой буквы
select to_char(sysdate, 'RM') from dual; -- римскими цифрами
Форматы недели:
select to_char(sysdate, 'WW') from dual; -- двузначный номер недели года
select to_char(sysdate, 'IW') from dual; -- двузначный номер недели года по ISO
select to_char(sysdate, 'W') from dual; -- однозначный номер недели месяца
Форматы дня:
select to_char(sysdate, 'DDD') from dual; -- трехзначный номер дня года
select to_char(sysdate, 'DD') from dual; -- двузначный номер дня месяца
select to_char(sysdate, 'D') from dual; -- однозначный номер дня недели
select to_char(sysdate, 'DAY') from dual; -- полное название дня в верхнем регистре
select to_char(sysdate, 'Day') from dual; -- полное название дня с заглавной буквы
select to_char(sysdate, 'DY') from dual; -- первые две буквы названия в верхнем регистре
select to_char(sysdate, 'Dy') from dual; -- первые две буквы названия с заглавной буквы
select to_char(sysdate, 'J') from dual; -- Юлианский день - число дней, прошедшее с 1 января 4713 г. до нашей эры
Формат часов:
select to_char(sysdate, 'HH24') from dual; -- двузначный номер часа в 24 часовом формате
select to_char(sysdate, 'HH24 PM') from dual; -- с суффиксом
select to_char(sysdate, 'HH') from dual; -- двузначный номер часа в 12 часовом формате
select to_char(sysdate, 'HH PM') from dual; -- с суффиксом
select to_char(sysdate, 'HH A.M.') from dual; -- с суффиксом
Форматы минут:
select to_char(sysdate, 'MI') from dual; -- двузначное количество минут
Форматы секунд:
select to_char(sysdate, 'SS') from dual; -- двузначное количество секунд
Существует тип TIMESTAMP, который может хранить дробную часть секунд.
Необязательную точность представления секунд можно определить параметром FF[1..9]
Значение этого параметра по умолчанию равно 6 (справа от десятичной точки секунд можно поместить до 6 цифр)
При попытке поместить большее количество цифр в дробную часть секунд, значение дробной части будет округлено.
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF') FROM dual; -- шесть цифр после десятичной точки (по умолчанию)
2014-10-18 08:55.42.050000
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF3') FROM dual; -- три цифры после десятичной точки
2014-10-18 08:56.23.606
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF9') FROM dual; -- девять цифр после десятичной точки
2014-10-18 08:56.55.526000000
select to_char(sysdate, 'SSSSS') from dual; -- число секунд отсчитываемое от полуночи
В отчетах statspack применяются следующие обозначения долей секунд:
second (s)
centisecond (cs) - 100th of a second
millisecond (ms) - 1,000th of a second
microsecond (us) - 1,000,000th of a second
Символы, позволяющие разделять аспекты дат и времени.
- / , . ; : или любой текст в кавычках "текст"
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI.SS') FROM dual;
2014-10-18 14:30.43
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD;HH24 "часов" MI "минут" SS "секунд"') FROM dual;
2014/10/18;14 часов 31 минут 18 секунд
AM или PM (A.M. или P.M.)
00:00 (полночь) 12:00 a.m.* (полночь)
12:00 (полдень) 12:00 p.m.* (полдень)
Проблемы в обозначениях полудня и полуночи:
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI.SS AM') FROM dual;
2014-10-18 14:53.58 PM
AD или BC (A.D. или B.C.)
BC - до нашей эры
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI.SS BC') FROM dual;
2014-10-18 15:00.25 Н.З.
TH - суффикс для чисел
SELECT TO_CHAR(SYSDATE, 'DDTH') FROM dual;
SELECT TO_CHAR(SYSDATE, 'ddTH') FROM dual;
SELECT TO_CHAR(SYSDATE, 'mmTH') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYYTH') FROM dual;
SELECT TO_CHAR(SYSDATE, 'yyyyTH-MMTH-DDTH HH24TH:miTH.SSTH BC') FROM dual;
2014th-10TH-18TH 17TH:56th.52ND Н.З.
SP - числовые значения записываются словами
SELECT TO_CHAR(SYSDATE, 'DDSP') FROM dual;
SELECT TO_CHAR(SYSDATE, 'ddSP') FROM dual;
SELECT TO_CHAR(SYSDATE, 'mmTHSP') FROM dual;
SELECT TO_CHAR(SYSDATE, 'mmSP') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYYTHSP') FROM dual;
TWO THOUSAND FOURTEENTH
SELECT TO_CHAR(SYSDATE, 'YYYYSP') FROM dual;
TWO THOUSAND FOURTEEN
EE - Полное название эпохи для японского календаря, календаря КНР и буддийского календаря.
E - Сокращенное название эпохи
select TO_DATE('H19-01-01' , 'EYY-MM-DD' , 'NLS_CALENDAR=''JAPANESE IMPERIAL''') e_date
from dual;
select TO_DATE('平成19-01-01' , 'EEYY-MM-DD' , 'NLS_CALENDAR=''JAPANESE IMPERIAL''') ee_date
from dual;
Часовые пояса:
В Oracle с версии 9i появилась возможность использовать различные часовые пояса.
Часовой пояс - это смещение от времени по Гринвичу(GMT).
Но теперь оно называется Всемирное скоординированное время(UTC).
Часовой пояс определяется либо как смещение относительно UTC, либо по имени региона (названию часового пояса).
Получить названия часовых поясов можно так:
select * from v$timezone_names;
Africa/Abidjan LMT
Africa/Abidjan GMT
Africa/Accra LMT
Africa/Accra GMT
Africa/Accra GHST
Africa/Addis_Ababa LMT
Africa/Addis_Ababa ADMT
Africa/Addis_Ababa EAT
Africa/Algiers LMT
Africa/Algiers PMT
Africa/Algiers WET
.
При определении смещения используется формат HH:MI с префиксом в виде знака + или -
+/- HH:MI
Посмотрим какое смещение относительно UTC установлено в нашей БД:
select dbtimezone from dual;
(меняется параметром time_zone в spfile.ora)
Часовой пояс сеанса можно определить так:
select sessiontimezone from dual;
Europe/Moscow
Его легко можно поменять на время сеанса:
alter session set time_zone = 'PST';
select sessiontimezone from dual;
Стандартное Тихоокеанское время PST отстает от UTC на восемь часов.
Восточное стандартное время EST отстает от UTC на пять часов.
Текущую дату для сеанса в локальном часовом поясе можно определить так:
select current_date from dual;
select to_char(current_date, 'YYYY-MM-DD HH24:MI.SS' ) from dual;
sysdate() - возвращает значение даты и времени, установленных в ОС компьютера, на котором размещена БД.
current_date() - возвращает значение даты и времени для часового пояса вашего сеанса.
Для любого часового пояса можно найти величину смещения с помощью функции tz_offset().
select tz_offset('PST') from dual;
select tz_offset('Europe/Moscow') from dual;
TZH - время в часах часового пояса
TZM - минуты часового пояса
TZR - регион часового пояса
TZD - часовой пояс с информацией о переходе на летнее время
Tип TIMESTAMP, в отличие от типа DATE, может хранить информацию о часовых поясах.
select to_char(SYSTIMESTAMP, 'TZH:TZM') from dual;
select to_char(SYSTIMESTAMP, 'TZR') from dual;
select to_char(SYSTIMESTAMP, 'TZD') from dual;
select to_char(SYSTIMESTAMP, 'HH:MI:SS.FFTZH:TZM') from dual;
select to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;
2014-10-18 10:52:19 +04:00
select to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD') from dual;
2014-10-18 10:52:31.802000 PM +04:00 +04:00
Чтобы конвертировать дату-время из одного часового пояса к другому,
можно воспользоваться функцией NEW_TIME().
Конвертация строки в тип дата-время.
Функцию TO_DATE(x [, формат])
можно использовать для конвертирования строки x в тип дата-время.
Если строка формата опущена, то дата должна быть представлена в формате по умолчанию:
DD-MON-YYYY или DD-MON-YY
(Вообще формат даты по умолчанию определяет параметр БД NLS_DATE_FORMAT)
alter session set NLS_DATE_LANGUAGE = 'AMERICAN' ;
alter session set NLS_DATE_FORMAT = 'SYYYY-MM-DD' ;
alter session set NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' ;
alter session set NLS_TIMESTAMP_TZ_FORMAT = 'SYYYY-MM-DD HH24:MI:SS TZH:TZM' ;
Можно и явно задать формат
select to_date('April 28, 1971' , 'MONTH DD, YYYY') from dual;
Совместное использование to_date() и to_char()
Формат даты по умолчанию, можно использовать и при вставке строк в таблицу:
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY';
NLS - параметры:
National language_support (До Oracle9i)
Globalisation support (Начиная с Oracle9i)
Кодировка устанавливается только в переменных окружения!
Язык - RUSSIAN, AMERICAN
SELECT * FROM v$nls_valid_values
WHERE parameter = 'LANGUAGE'
ORDER BY value
CIS - СНГ
1. первый день недели
2. символ национальной валюты
(Если явно не задан параметр NLS_CURRENCY)
3. Десятичный и групповой разделители чисел
SELECT * FROM v$nls_valid_values
WHERE parameter = 'TERRITORY'
ORDER BY value
SELECT * FROM v$nls_valid_values
WHERE parameter = 'CHARACTERSET'
-- Русский язык, Кириллица
AND (value LIKE 'CL%'
OR
value LIKE 'RU%')
ORDER BY value
WE8ISO8859P1 - Западная Европа
Какие есть параметры NLS?
SELECT * FROM nls_session_parameters
PARAMETER VALUE
================ ==========
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=CIS
-- Символ нац. валюты
NLS_CURRENCY='р.'
-- Символ нац. валюты по стандарту ISO
NLS_ISO_CURRENCY='CIS'
-- Десятичный разделитель и разделитель групп
NLS_NUMERIC_CHARACTERS=', '
-- Календарь
NLS_CALENDAR=GREGORIAN
-- Формат ввода и вывода даты по-умолчанию
NLS_DATE_FORMAT='DD.MM.RR'
-- Язык для вывода названий месяцев и дней недели
NLS_DATE_LANGUAGE='AMERICAN'
-- Тип Сортировки
NLS_SORT=BINARY
-- . (нет описания)
NLS_TIME_FORMAT='HH24:MI:SSXFF'
-- Формат ввода и вывода даты типа TIMESTAMP по-умолчанию
NLS_TIMESTAMP_FORMAT='DD.MM.RR HH24:MI:SSXFF'
-- . (нет описания)
NLS_TIME_TZ_FORMAT='HH24:MI:SSXFF TZR'
-- Формат ввода и вывода даты типа TIMESTAMP с временнОй зоной по-умолчанию
NLS_TIMESTAMP_TZ_FORMAT='DD.MM.RR HH24:MI:SSXFF TZR'
-- Замещает символ нац. валюты, установленный по умолчанию параметром NLS_TERRITORY
NLS_DUAL_CURRENCY='р.'
-- Как сравнивать строки BINARY или ASCII (по правилам нац. алфавита)
NLS_COMP=BINARY
-- CHAR по умолчанию в байтах или в символах
NLS_LENGTH_SEMANTICS=BYTE
-- NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is
-- data loss during an implicit OR explicit CHARACTER TYPE conversion.
-- The DEFAULT value results IN no error being reported.
NLS_NCHAR_CONV_EXCP=FALSE
Как можно устанавливать значения параметров NLS?
1. В системном реестре Windows
2. Установить переменные окружения
Для Windows (в bat-файле)
SET NLS_DATE_LANGUAGE=RUSSIAN
SET NLS_LANG=AMERICAN_CIS.CL8MSWIN1251
sqlplus .
3. ALTER SESSION SET
NLS_DATE_LANGUAGE=RUSSIAN
NLS_DATE_FORMAT='DD.MM.YYYY';
SELECT TO_CHAR(SYSDATE, 'Month day')
FROM dual
Посмотреть nls-параметры сессии, базы данных и инстанса можно так:
select * from
(select 'SESSION' SCOPE,s.* from nls_session_parameters s
union
select 'DATABASE' SCOPE,d.* from nls_database_parameters d
union
select 'INSTANCE' SCOPE,i.* from nls_instance_parameters i
) a
pivot (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in ('SESSION' as "SESSION",'DATABASE' as "DATABASE",'INSTANCE' as "INSTANCE"));
Функции для работы с типом data.
ADD_MONTHS(data, n)
Позволяет добавить к дате целое количество месяцев (или отнять, если n отрицательное)
SELECT ADD_MONTHS('28.04.1971' , 13) FROM DUAL; -- Добавить 13 месяцев
SELECT ADD_MONTHS('28.04.1971' , -12) FROM DUAL; -- Отнять 12 месяцев
Читайте также: