Oracle преобразование даты в минуты. Встроенные функции Oracle
Функции работы с датами
Функция |
Возвращаемое значение |
ADD-MONTHS (d,n) |
Дата d плюс n месяцев. |
Последнее число месяца, указанного в d |
|
MONTHS-BETWEEN (d,e) |
Число месяцев между датами d1 и d2. |
NEW-TIME (d,a,b) |
Дата и время в часовом поясе a, соответствующие дате и времени в часовом поясе b, при этом d,a и b значения типа CHAR, определяющие часовые пояса. |
NEW-DAY (d,char) |
Дата первого после даты (/дня недели, название которого записано в с1шг. |
Текущая дата и время. |
Усечение и округление дат
Форматные маски дат для функций ROUND и TRUNC.
В таблице перечислены форматные маски, которые можно использовать в функциях ROUND и TRUNC. По умолчанию используется форматная маска "DD".
Форматная маска |
Возвращаемое значение |
Первый день столетия |
|
SYYYY или YYYY или YYY или YY или Y или YEAR или SYEAR |
|
Первый день квартала (округляется до 16 числа второго месяца квартала) |
|
MONTH или MON или MM или RM |
Первый день месяца (округляется до 16 числа) |
Тот же день недели, что и первый день текущего года |
|
Тот же день недели, что и первый день текущего месяца |
|
DDD или DDD или J |
|
DAY или DY или D |
Первый день недели |
Функции преобразования
Возвращаемое значение |
|
CHARTOROWID(char) |
Char преобразуется из типа данных CHAR в тип данных ROWID |
CONVERT(char, dest_char_set [,source_char_set]) |
Преобразует символьную строку из набора символов source_char_set в набор символов dest_char_set |
Преобразует значение char, содержащее шестнадцатиричные цифры, в значение типа данных RAW |
|
Преобразует raw в символьное значение, содержащее его шестнадцатиричный эквивалент |
|
ROWIDTOCHAR (rowid) |
Преобразует значение типа ROWID в значение типа CHAR |
TO_CHAR (expr [,fmt [,"nls_num_fmt"]]) |
Преобразует значение expr типа DATE или NUMBER в значение типа CHAR по формату форматной маски fmt. Если fmt отсутствует, значения типа DATE преобразуются по формату, заданному по умолчанию, и значения типа NUMBER- в значение типа CHAR с шириной, достаточной для того, чтобы вместить все значащие цифры. Значение "nls_num_fmt" определяет связанные с языком форматные маски. В Trusted ORACLE преобразует значения MLS или MLS_LABEL в значение типа VARCHAR2 |
TO_DATE (char[,fmt [,"nls_lang"]]) |
Преобразует char в значение типа DATE с помощью форматной маски fmt. Если fmt опускается, используется форматная маска для даты, принятая по умолчанию."nls_ang" задает язык, используемый в названиях месяцев и дней |
TO_MULTI_BYTE (char) |
Преобразует однобайтовые символы, имеющие многобайтовые эквиваленты, в соответствующие многобайтовые символы |
TO_NUMBER (char [,fmt [,"nls_lang"]]) |
Преобразует char, содержащее число в формате, указанном параметром fmt, в значение типа NUMBER. "nls_lang" задает язык, определяющий символы валют и числовые разделители |
TO_SINGLE_BYTE (char) |
Преобразует многобайтовые символы, имеющие однобайтовые эквиваленты, в соответствующие однобайтовые символы |
Форматные маски.
Этот раздел описывет форматные маски дат и чисел.
Форматные маски дат в TO_CHAR и TO_DATE.
Элементы форматной маски даты перечислены в приведенной ниже таблице. Любую комбинацию этих элементов можно использовать как аргумент fmt функций TO_CHAR или TO_DATE. По умолчанию fmt равен "DD-MON-YY".
Элемент формата |
Возвращаемое значение |
Столетие; если указано "S" то перед датами до нашей эры ставится "-". |
|
YYYY или SYYYY |
Год; если указано"S" то перед датами до нашей эры ставится "-". YYY или YY или Y] Последние 3, 2, или1 цифра года. |
4 цифры года по стандарту ISO. IYY или IY или I] Последние 3, 2, или1 цифра года по стандарту ISO. |
|
Год с запятой в указанной позиции. |
|
SYEAR или YEAR |
Год, записанный словами, а не цифрами; если указано"S" то перед датами до нашей эры ставится "-". |
Последние 2 цифры года; для указания года в других столетиях. |
|
BC- до нашей эры(до н.э.); AD - нашей эры |
|
B.C. или A.D. |
B.C.- до нашей эры(до н.э.); A.D. - нашей эры |
Нумерация месяцев римскими цифрами(I-XII; JAN=I). |
|
Название месяца, дополненное пробелами до 9-ти символов. |
|
Сокращенное название месяца. |
|
Неделя года (1-52) или месяца (1-5). |
|
Неделя года (1-52 или 1-53) по стандарту ISO. |
|
DDD или DD или D |
День года (1-366) или месяца (1-31) или недели (1-7). |
Название дня, дополненное пробелами до 9-ти символов. |
|
Сокращенное название дня. |
|
Дата юлианского календаря; число дней, считая с первого января 4712 года до н.э. |
|
AM -до полудня,PM- после полудня |
|
A.M. или P.M. |
A.M. -до полудня,P.M.- после полудня |
Час дня (1-12). |
|
Час дня (0-23). |
|
Минута (0-59) |
|
Секунда (0-59) или количество секунд после полуночи (0-86399). |
|
Знаки пунктуации. |
|
"...текст..." |
Текст воспр в возвращенном значении. |
Префикы и суффиксы элементов формата даты
К элементам формата даты можно добавлять следующие префиксы:
К элементам формата даты можно добавлятть следующие суффиксы:
Прописные и строчные буквы в элементах формата даты.
Следующие строки задают вывод прописными буквами, вывод прописными буквами только начальных букв слов, или вывод строчными буквами.
Если к элементу формата даты добавляется префикс или суффикс, то регистр (прописные, строчные буквы) определяется элементом формаиа, а не префиксом или суффиксом. Например, "ddTH " задает "04th" а не "04TH".
Элементы формата числа для TO_CHAR
В следующей таблице перечислены элементы формата числа. Комбинацию этих элементов можно использовать как аргумент fnu функции TO_CHAR.
Элемент |
Пример |
Описание |
Количество девяток указывает число возвращаемых значащих цифр. |
||
Добавляет нули перед числом. |
||
Добавляет знак доллара перед числом. |
||
Заменяет нулевые значения пробелами. |
||
Возвращает знак "-" после отрицательных значении. |
||
Возвращает знак "+" для положительных значений и знак "-" для отрицательных значений в указанную позицию. |
||
Возвращает отрицательные значения в <угловых скобках>. |
||
Возвращает символ, представляющий десятичную точку, в указанную позицию. |
||
Возвращает символ разделения цифр на группы в указанную позицию. |
||
Возвращает международной знак валюты в указанную позицию. |
||
Возвращает знак местной валюты в указанную позицию. |
||
Возвращает запятую в указанную позицию. |
||
Возвращает точку в указанную позицию. |
||
Умножает значение на 10 n , где n количество девяток после "V". |
||
Возвращает значение в нормализованной форме. В fnu должно быть ровно четыре буквы "Е". |
||
Возвращает римские цифры прописными или строчными буквами (целое число в диапазоне от 1 до 3999). |
||
Возвращает значение, преобразованное из даты юлианского календаря в формат "MM/DD/YY". |
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.
Как вытащить дату из базы данных в удобном формате d.m.Y. (день.месяц.год) Oracle . Нам поможет функция to_char(). Благодаря ей можно преобразовать время в нужный формат. Посмотрите на пример.
SELECT to_char(current_timestamp, "DD.MM.YYYY") as date_create from DUAL
Формат времени DD.MM.YYYY (день.месяц.год). Вы можете использовать любые символы в качестве разделителей: /, :, -.
Для вывода текущего времени в формате день.месяц.год часы:минуты:секунды используйте параметры DD.MM.YYYY HH24:MI:SS.
SELECT to_char(current_timestamp, "DD.MM.YYYY HH24:MI:SS") as date_create from DUAL /* вывод: 06.10.2017 16:50:52 */
Допустим, нужно вывести начало года? У вас автоматически будет подставляться текущий год. В этом примере применил конкатенацию ||. Более подробную информацию вы найдёте на странице TO_CHAR ФУНКЦИЯ . На той странице находится большой список параметров для преобразования даты в строку.
SELECT "01.01." || to_char(current_timestamp, "YYYY") as date_create from DUAL
Просмотры: 1762, уровень: лёгкий уровень, рейтинг: 0 , дата: 2016-10-26 14:07:32
В данном разделе речь пойдет о функциях работы с датой/временем и функциями преобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная - время. Это позволяет совершать над датами арифметические операции - сложение и вычитание.
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера. Пример:
SELECT
SYSDATE
FROM dual
SYSDATE
26.12.2007
16:24:43
Функция ADD_MONTHS(d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров х, причем х может быть отрицательным - в этом случае указанное количество месяцев вычитается из заданной даты.
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1, ADD_MONTHS(SYSDATE,
-3)
d2
FROM dual
26.12.2007 16:24:43 |
26.03.2008 16:24:43 |
26.09.2007 16:24:43 |
Функция LAST _DAY(d)
Возвращает последнее число месяца, указанного в дате d. Пример:
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
FROM dual
26.12.2007 16:24:43 |
31.12.2008 16:24:43 |
Данная функция очень удобна для определения количества дней в заданном месяце, например:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), "DD") d1
FROM dual
26.12.2007 16:24:43 |
Функция MONTHS_BETWEEN(dl, d2)
Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами dl и d2 с учетом знака как dl-d2, возвращаемое число является дробным.
SELECT MONTHS_BETWEEN("2.09.2006",
"2.05.2006") d1,
M0NTHS_BETWEEN("12.09.2006", "2.05.2006") d2,
M0NTHS_BETWEEN("2.05.2006", "12.09.2006") d3
FROM dual
4,32258064516129 |
4,32258064516129 |
Функция TRUN С (d[,mask ])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
SELECT SYSDATE
d1,
TRUNC
(SYSDATE)
d2
FROM
dual
26.09.2006 16:45:26 |
Рассмотрим типовые примеры - усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE
dl,
TRUNC(SYSDATE, "HH24") d2,
TRUNC(SYSDATE, "DD") d3,
TRUNC(SYSDATE, "MM") d4,
TRUNC(SYSDATE, "YYYY") d5
FROM dual
26.09.2006 16:49:21 |
26.09.2006 16:00:00 |
26.09.2006 01.09.2006 01.01.2006 |
Функция ROUND(d[,mask])
Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD». Пример:
SELECT
SYSDATE
d
1,
ROUND(SYSDATE)
d2,
ROUND(SYSDATE,
"
НН
24")
d3,
ROUND
(SYSDATE
, "
DD
")
d4
,
ROUND(SYSDATE,
"MM")
d5
FROM
dual
26.09.2006 16:50:50 |
27.09.2006 26.09.2006 17:00:00 |
27.09.2006 01.10.2006 |
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения:
Назначение |
||
Первый день столетия |
||
YEAR, или YYYY, |
Первый день года |
|
или YY, или Y |
||
Первый день квартала |
||
MONTH, или MON, |
Первый день месяца |
|
Тот же день недели, что день текущего года |
||
Тот же день недели, что день текущего месяца |
||
DAY, или DY, или D |
Первый день недели |
|
НН, или НН12, или |
||
Функция TO_DATE(str[,mask [,nls_lang]])
Функция ТО_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр - язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной
маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» - она возникает при указании недопустимой форматной маски. Пример:
SELECT T0_DATE("12.09.2006")
d
FROM
dual
Функция TO_CHAR(d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан». Пример:
SELECT SYSDATE d1,
TOLCHAR(SYSDATE, "DD.MM.YY HH24:MI") d2
FROM
dual