Oracle добавить месяц к дате
Функция SYSDATE
Функция SYSDATE возвращает текущую дату и время по часам сервера.
Пример:
SELECT SYSDATE FROM dual;
Функция ADD_MONTHS (d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров x, причем x может быть отрицательным — в этом случае указанное количество месяцев вычитается из заданной даты.
Пример:
SELECT SYSDATE d, ADD_MONTHS ( SYSDATE , 5) d1, ADD_MONTHS ( SYSDATE , -5) d2 FROM dual;
Функция LAST_DAY (d)
Возвращает последнее число месяца, указанного в дате d.
Пример:
SELECT SYSDATE d, LAST_DAY ( SYSDATE ) d1 FROM dual;
Функция MONTHS_BETWEEN (d1, d2)
Функция MONTHS_BETWEEN возвращает количество месяцев между двумя датами d1 и d2 с учетом знака как d1-d2, возвращаемое число является дробным.
SELECT MONTHS_BETWEEN ( '2.09.2011' , '2.05.2010' ) d1, MONTHS_BETWEEN ( '12.09.2017' , '2.05.2012' ) d2, MONTHS_BETWEEN ( '2.05.2018' , '12.09.2015' ) d3
FROM dual;
Функция TRUNC (d[,mask])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
Пример:
SELECT SYSDATE d1, TRUNC ( SYSDATE ) d2 FROM dual;
Усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC ( SYSDATE , 'HH24' ) d2,
TRUNC ( SYSDATE , 'DD' ) d3,
TRUNC ( SYSDATE , 'MM' ) d4,
TRUNC ( SYSDATE , 'YYYY' ) d5
FROM dual;
Функция ROUND (d[,mask])
Функция ROUND аналогична TRUNC , но вместо усечения она производит округление. Форматная маска по умолчанию равна 'DD' .
Пример:
SELECT SYSDATE d1,
ROUND ( SYSDATE ) d2,
ROUND ( SYSDATE , 'HH24' ) d3,
ROUND ( SYSDATE , 'DD' ) d4,
ROUND ( SYSDATE , 'MM' ) d5,
ROUND ( SYSDATE , 'YYYY' ) d6
FROM dual;
Форматные маски для функций TRUNC и ROUND :
СС - Первый день столетия
YEAR, YYYY, YY, Y - Первый день года
Q - Первый день квартала
MONTH, MON, MM - Первый день месяца
WW - Тот же день недели, что и первый день текущего года
W - Тот же день недели, что и первый день текущего месяца
DDD, DD - День
DAY, DY, D - Первый день недели
HH, HH12, HH24 - Час
MI - Минута
Функция TO_DATE (str[,mask [,nls_lang]])
Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр — язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» — она возникает при указании недопустимой форматной маски.
Пример:
SELECT TO_DATE ( '12.09.2006' ) d FROM dual;
Функция TO_CHAR (d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан».
Пример:
SELECT SYSDATE d1, TO_CHAR ( SYSDATE , 'DD.MM.YY HH24:MI' ) d2 FROM dual;
Форматные маски функции TO_CHAR :
СС - Столетие.
SСС - Столетие, перед датами до нашей эры ставится знак «минус».
YYYY - Год.
SYYYY - Год, перед датами до нашей эры ставится знак «минус».
YYY, YY, Y - Год, последние 3,2 или 1 цифры года.
SYEAR, YEAR - Год, записанный прописью c учетом текущего национального языка.
Q - Номер квартала.
MM - Месяц.
RM - Номер месяца, записанный с имитацией римских цифр при помощи символов X и I.
MONTH - Название месяца прописью.
MON - Название месяца прописью в сокращенном до трех символов виде.
WW - Неделя года.
W - Неделя месяца.
DDD - День года.
DD - День месяца.
D - День недели.
DAY - День недели прописью, при необходимости дополняется до девяти символов пробелами.
DY - День недели прописью, сокращенный до трех символов.
J - Дата юлианского календаря. Является числом дней от 1.01.4712 до нашей эры.
HH24 - Час дня по 24-часовой шкале (0-23).
HH12, HH - Час дня по 12-часовой шкале (1-12).
AM, PM - Вывод признака «до полудня» — AM и «после полудня» — PM. Указание AM и PM в форматной маске равнозначно.
MI - Минуты (0-59).
SS - Секунды (0-59).
SSSSS - Количество секунд после полуночи.
-/. ; - Знаки пунктуации. Они выводятся в соответствующие места отформатированной даты.
SP - Форматный суффикс. Его добавление к элементу форматной маски, возвращающему число, приводит к форматированию этого числа прописью.
В БД Oracle для работы с датами предназначены 2 типа - DATE и TIMESTAMP .
Отдельно можно упомянуть INTERVAL - интервальный тип, который хранит диапазон между двумя датами.
Тип DATE
Тип DATE используется чаще всего, когда необходимо работать с датами в БД Oracle. Он позволяет хранить даты с точностью до секунд.
Некоторые БД, например MySQL, также имеют тип DATE, но там может храниться дата лишь с точностью до дня.
Приведение строки к дате
Одна из часто встречающихся ситуаций - необходимость представить строку в виде типа данных DATE . Делается это при помощи функции to_date . Данная функция принимает 2 параметра - строку, содержащую в себе собственно дату, и строку, которая указывает, как нужно интерпретировать первый параметр, т.е. где в этой дате год, где месяц, число и т.п.
На самом деле, функция to_date может работать и без строки с форматом даты, а также с еще одним дополнительным параметром, который будет указывать формат языка, но мы будем рассматривать вариант с двумя параметрами. Более детально ознакомиться с функцией to_date можно вот здесь.
Как видно, строка, определяющая формат даты, имеет очень большое значение. В примере выше, мы получили две разные даты, изменив лишь их формат в функции to_date .
Функция SYSDATE
Данная функция возвращает текущую дату. В зависимости от того, когда следующий запрос выполнится, значение SYSDATE будет всегда разным.
Приведение даты к строке
Чтобы отобразить дату в нужном нам формате, используется функция to_char .
Trunc
Функция trunc округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.
Если не указывать формат округления, то trunc округлит до дней, т.е. колонки "2" и "3" будут содержать одинаковое значение.
ADD_MONTHS
Функция add_months добавляет указанное количество месяцев к дате. Для того, чтобы отнять месяцы от даты, нужно передать в качестве второго параметра отрицательное число:
Разница между датами
Если просто отнять от одной даты другую, то мы получим разницу между ними в днях. Также, к датам можно прибавлять и отнимать обычные числа, и Oracle будет оперировать ими как днями:
Months_between
Функция months_between возвращает разницу между датами в месяцах:
Тип TIMESTAMP
Тип TIMESTAMP является расширением типа DATE . Он также, как и тип DATE , позволяет хранить год, месяц, день, часы, минуты и секунды. Но пимимо всего этого в TIMESTAMP можно хранить доли секунды.
TIMESTAMP - максимально точный тип данных для хранения даты, точнее в ORACLE уже нет.
При описании колонки с типом TIMESTAMP можно указать точность, с которой будут храниться доли секунды. Это может быть число от 0 до 9. По умолчанию это значение равно 6.
Пример создания таблицы с колонкой типа TIMESTAMP :
Колонка logout_time может хранить доли секунды с точностью до 6 знаков после запятой, а колонка login_time - с точностью до 8 знаков.
SYSTIMESTAMP
Данная функция работает так же, как и SYSDATE , только она возвращает текущую дату в формате TIMESTAMP :
EXTRACT
Функция extract позволяет извлечь из даты определенные составные части, например получить только год, или только месяц и т.п.
Извлекаемые части имеют числовой тип данных, т.е. колонки year , month и day всего лишь числа.
Несмотря на то, что тип DATE хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:
В ответ мы получим ошибку ORA-30076: invalid extract field for extract source .
Но если использовать тип TIMESTAMP , то помимо года, месяца и дня с помощью функции EXTRACT можно по отдельности получить значение часов, минут и секунд:
Приведение строки к timestamp
Для приведения строки к типу timestamp используется фукнция TO_TIMESTAMP :
В запросе выше следует обратить внимание на то, как указывается точность долей секунды. ff3 будет сохранять точность до тысячных долей секунды, ff9 - до максимальных 9-и разрядов.
Форматы строк для приведения к датам очень разнообразны. Здесь приведены варианты, которые чаще всего понадобятся на практике. Ознакомиться со всеми форматами строк можно в докумениации.
В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная — время. Это позволяет совершать над датами арифметические операции — сложение и вычитание.
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.
SYSDATE
Функция ADD_MONTHS(d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров x, причем x может быть отрицательным — в этом случае указанное количество месяцев вычитается из заданной даты.
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1,
ADD_MONTHS(SYSDATE, -3) d2
D
D1
D2
Функция LAST_DAY(d)
Возвращает последнее число месяца, указанного в дате d.
SELECT SYSDATE d,
D
D1
Данная функция очень удобна для определения количества дней в заданном месяце, например:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), 'DD') d1
D
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
D1
D2
D3
Функция TRUNC(d[,mask])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
SELECT SYSDATE d1,
D1
D2
Рассмотрим типовые примеры — усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC(SYSDATE, 'HH24') d2,
TRUNC(SYSDATE, 'DD') d3,
TRUNC(SYSDATE, 'MM') d4,
TRUNC(SYSDATE, 'YYYY') d5
D1
D2
D3
D4
D5
Функция ROUND(d[,mask])
Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD».
SELECT SYSDATE d1,
ROUND(SYSDATE, 'HH24') d3,
ROUND(SYSDATE, 'DD') d4,
ROUND(SYSDATE, 'MM') d5,
ROUND(SYSDATE, 'YYYY') d6
D1
D2
D3
D4
D5
D6
Форматные маски, допустимые для функций 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
D
Функция TO_CHAR(d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'DD.MM.YY HH24:MI') d2
D1
D2
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Маска
Назначение
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'CC') d2
D1
D2
Столетие, причем перед датами до нашей эры ставится знак «минус».
SELECT SYSDATE d1,
TO_CHAR(SYSDATE-1000000, 'SCC') d2
D1
D2
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'YYYY') d2
D1
D2
Аналогично YYYY, но перед датами до нашей эры ставится знак «минус»
Аналогичны YYYY, но возвращаются соответственно последние 3,2 или 1 цифра года.
Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:
Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.
Интервал времени
Синтаксис и реализация интервалов отличается на разных СУБД.
Oracle
PostgreSQL
интервалы указываются в виде строки, в которой перечисляются значение и тип промежутка:
Слова можно употреблять и во множественном числе.
Если интервал начинается с дней, то можно использовать короткий формат строки как
в Oracle для дневных интервалов.
MySQL
Только сложные интервалы, состоящие из более одного типа промежутков, указываются в строке.
Для этих целей введены дополнительные по сравнению с PostgreSQL имена для промежутков:
MySQL интервалы используются в выражениях с временными типами данных,
использовать их в качестве конечного типа для столбцов запрещено.
Выражения и операции
cтроковые операции
алгебраические операции
Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.
операции отношения
логические операции и предикаты
выражение IN (значение1. значениеn)
В качестве множества значений может служить корректная выборка
условные выражения
Ниже приведен пример использования выражения в запросе выбора данных.
прочие операции
В каждой СУБД свой набор операций, выше были приведены наиболее употребительные.
Например, в PosgreSQL можно использовать и такие операции:
Обзор функций
математические функции
Тригонометрические функции работают с радианами:
строковые функции
работа с датами
В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:
Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:
Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов.
Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.
Преобразование типов
Множество типов разрешенные для преобразования в констркуции CAST AS определяется
реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)],
char[(n)], date, datetime, decimal[(m[,d])], signed [integer], time, unsigned [integer].
А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со
множеством записей в массивы.
В PostgreSQL более расширенные возможности по преобразованию. Во-первых, можно добавить
собственное преобразование для встроенных и пользовательских типов. Во-вторых, есть
собственный более удобный оператор преобразования типов .
В большинстве случае необходимо преобразование в строку либо из строки. Для этого случаяСУБД предоставляют дополнительные функции.
функции Oracle
Формат числовой строки задается следующими элементами:
функции PostgreSQL
Основные элементы форматирования совпадают с Oracle.
функции MySQL
При хранении даты в MySQL под типом Date (), она имеет формат 2011-07-11 (год-месяц-день). В некоторых случаях даже не имея разделителя 20110711.
Поскольку в русскоязычных странах более привычным к восприятию считается формат 11.07.2011 (день.месяц.год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.
Преобразовать дату можно несколькими способами.
- при помощи php кода
- воспользовавшись командой DATE_FORMAT () при выборке из базы.
Первый способ применяется в тех случаях, когда необходимо вывести небольшое количество записей или же когда разработчик не подозревает о существовании второго способа.
Второй способ применим во всех случаях, вне зависимости сколько записей необходимо извлечь из базы, при этом он осуществляет минимальную нагрузку на сервер в отличии от способа с php кодом.
Рассмотрим пример выполнения:
Допустим существует таблица message , которая содержит ячейку send_data с датой в формате 2011-07-11 .
Для извлечения и преобразования даты напишем следующий код:
Далее в том месте где необходимо вывести преобразованную дату, выводим массив $message любой, удобной для вас командой:
к примеру если в send_data находится 2011-05-03 то мы получим 03.05.2011 .
Номер индекса в массиве $message указываем каким по счету начиная от 0, в команде SELECT извлекается необходимое значение с преобразованной датой. К примеру при запросе:
вывод даты будет осуществляться с индексом 2:
Преобразовать дату при помощи DATE_FORMAT() можно в любой вид и очередность при помощи подстановки ключей.
Ниже приведен список основных элементов форматирования для даты и времени:
Читайте также: