Посчитать разницу между временем sql oracle

Обновлено: 07.07.2024

До 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 — новый элемент маски формата данных FF6 предназначен для задания долей секунд. Если вы укажете точность меньше той что хранится в БД, 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 сводятся к следующему набору:

Прибавление или вычитание интервала из значения даты/времени.
Вычитание одного значения даты/времени из другого для определения интервала между двумя значениями.
Прибавление или вычитание одного интервала из другого.
Умножение или деление интервала на число.

По историческим причинам я раздельно рассматриваю арифметические операции со значениями типа DATE и операции, в которых задействованы типы семейств TIMESTAMP и INTERVAL .

Операции с типами TIMESTAMP и INTERVAL

Вычисления с интервалами «дни/секунды» легко выполняются при работе с типами данных семейства TIMESTAMP . Создайте значение INTERVAL DAY TO SECOND и используйте его при сложении и вычитании. Например, прибавление к текущей дате 1500 дней, 4 часов, 30 минут и 2 секунд выполняется следующим образом:

С интервалами «годы/месяцы» дело обстоит сложнее. Продолжительность любого дня составляет 24 часа, или 1440 минут, или даже 86 400 секунд, но не все месяцы имеют одинаковую продолжительность в днях: 28, 29, 30 или 31 день. По этой причине простое прибавление одного месяца к дате может привести к неоднозначному результату. Допустим, вы прибавляете один месяц к последнему дню мая; что получится — последний день июня или недействительная дата 31 июня? Все зависит от того, что должны представлять интервалы.

Вычисления с типом INTERVAL YEAR TO MONTH лучше зарезервировать для тех значений даты/времени, которые усекаются по началу месяца или, скажем, по 15 числу — они плохо подходят для конца месяца. Если вам потребуется прибавить или вычесть сколько-то месяцев (или лет — аналогичная проблема возникает при прибавлении одного кода к 29 февраля 2008 года) из даты, в которой может быть задействован конец месяца, используйте функцию ADD_MONTHS . Эта функция, возвращающая тип DATE , решает проблему преобразованием соответствующих дат в последний день месяца вместо выдачи ошибки. Например, ADD_MONTHS('31-May-2008',1) вернет 30 июня 2008 года. Полученное значение DATE не содержит данных часового пояса (или долей секунд); если в результате должны присутствовать эти компоненты, вам придется реализовать дополнительную логику извлечения и повторного внесения этих компонентов в результат.

Аналогичной функции вычитания SUBTRACT_MONTHS не существует, но ADD_MONTHS можно вызывать с отрицательным количеством месяцев. Например, вызов ADD_MONTHS(current_date, -1) в приведенном примере вернет дату за один месяц до последнего дня апреля.

Операции с типом DATE

В операциях с типом DATE можно использовать как значения INTERVAL , так и числовые значения, представляющие дни и их доли. Например, прибавление одного дня к текущей дате и времени выполняется так:

Прибавление четырех часов к текущей дате и времени:

Обратите внимание на использование дроби 4/24 вместо 1/6. При чтении кода сразу становится ясно, что значение, возвращаемое SYSDATE , увеличивается на 4 часа; а если использовать 1/6, программист, который будет заниматься сопровождением кода, будет долго ломать голову над тем, что должна означать эта таинственная дробь. Для еще более явного выражения намерений можно воспользоваться именованной константой:

В табл. 10.4 приведены дробные значения, представляющие часы, минуты и секунды при работе с DATE . Также в нее включены некоторые дробные значения, которые могут использоваться для построения этих значений.

Значение	Выражение	Представляет
1/24	1/24	Один час
1/1440	1/24/60	Одна минута
1/86400	1/24/60/60	Одна секунда

Используйте значения из табл. 10.4, и ваш код станет более понятным. Программист, привыкший к этим трем делителям, без труда поймет, что 40/86400 означает 40 секунд. Понять, что дробь 1/21610 означает то же самое, будет намного сложнее.

Вычисление интервала между двумя значениями DATE

Интервал между двумя значениями из семейства TIMESTAMP вычисляется простым вычитанием одного значения из другого. Результат всегда относится к типу INTERVAL DAY TO SECOND. Например:

Интервалы могут быть отрицательными или положительными. Отрицательный интервал означает, что более недавняя дата вычитается из даты, находящейся в прошлом:

Знак результата задает направленность интервала. К сожалению, не существует функции абсолютного значения для интервалов — аналога функции ABS для числовых значений.

Если вы вычисляете интервал между двумя значениями типа DATE , результатом является количество 24-часовых периодов (не то же самое, что количество дней) между ними. Если получено целочисленное значение, то разность представляет точное количество дней. Если же значение является дробным, то разность включает также некоторое количество часов, минут и секунд. Следующий пример взят из предыдущего раздела, но в нем используется тип DATE :

Три дня — понятно, но что такое 0.40208333333333333333333333333333333333? Даты часто усекаются перед вычитанием или же усекается полученный результат. Использование типов INTERVAL и TIMESTAMP значительно упрощает правильное усечение длинной дробной строки до часов, минут и секунд.

Для вычисления интервалов между двумя значениями DATE также удобно пользоваться функцией MONTHS_BETWEEN . Функция имеет следующий синтаксис:

Функция MONTHS_BETWEEN работает по следующим правилам:

Если дата_1 наступает позже даты_2 , MONTHS_BETWEEN возвращает положительное число.
Если дата_1 наступает раньше даты_2 , MONTHS_BETWEEN возвращает отрицательное число.
Если дата_1 и дата_2 относятся к одному месяцу, функция возвращает дробное значение из диапазона от –1 до +1.
Если дата_1 и дата_2 приходятся на последние дни соответствующих месяцев, функция возвращает целое число (без дробного компонента).
Если дата_1 и дата_2 относятся к разным месяцам и хотя бы одна из них не приходится на последний день месяца, функция возвращает дробное значение. (Дробный компонент вычисляется для месяцев, состоящих из 31 дня, с учетом разницы компонентов времени двух дат.)

Несколько примеров использования MONTHS_BETWEEN :

Вероятно, вы заметили здесь определенную закономерность. Как уже было сказано, функция MONTHS_BETWEEN вычисляет дробный компонент количества месяцев исходя из предположения, что каждый месяц содержит 31 день. Поэтому на каждый день сверх полного месяца к результату прибавляется 1/31 месяца:

В соответствии с этим правилом количество месяцев между 31 января 1994 года и 28 февраля 1994 года равно 1 — удобное целое число. Однако количество месяцев между 31 января 1994 года и 1 марта 1994 года увеличивается на .032258065. Как и в случае с вычитанием DATE , при работе с MONTHS_BETWEEN часто используется функция TRUNC .

Смешанное использование DATE и TIMESTAMP

Результатом вычитания двух TIMESTAMP является значение типа INTERVAL DAY TO SECOND . Результат вычитания с двумя значениями DATE представляет собой числовое значение. Соответственно, если требуется вычесть одно значение DATE из другого и вернуть значение INTERVAL DAY TO SECOND , вам придется преобразовать DATE в TIMESTAMP функцией CAST . Пример:

Если значения DATE и TIMESTAMP смешиваются в одном выражении вычитания, PL/SQL выполняет неявное преобразование DATE в TIMESTAMP . Пример:

Как обычно при работе с типами данных даты и времени, в программе желательно использовать явные преобразования.

Сложение и вычитание интервалов

В отличие от значений даты/времени, операция суммирования интервалов выглядит вполне разумно. Также имеет смысл и вычитание одного интервала из другого. Необходимо лишь помнить, что интервалы, участвующие в суммировании или вычитании, должны относиться к одному типу. Например:

Пример демонстрирует результаты трех вычитаний интервалов. В первых двух операциях участвуют интервалы INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH . В третьей операции используется вычитание двух чисел. Запомните: при работе с типами DATE интервал между двумя значениями DATE выражается типом NUMBER . Так как месяц может состоять из 28, 29, 30 или 31 дня, при попытке суммирования или вычитания интервала «дни/секунды» с интервалом «годы/месяцы» происходит ошибка.

Умножение и деление интервалов

Операции умножения и деления не применимы к датам, но зато интервал можно умножить или разделить на число. Несколько примеров:

Типы данных INTERVAL без ограничений

Интервалы можно объявлять с разным уровнем точности, причем значения разной точностью не полностью совместимы между собой. Проблема особенно наглядно проявляется при написании процедур и функций, получающих параметры типа INTERVAL . Обратите внимание на потерю точности в следующем примере, где значение переменной dts удваивается с помощью функции double_my_interval :

Результат выполнения кода:

Цифры были потеряны не только в дробной части секунд, но и в значении количества дней. А если бы переменной dts было присвоено значение, равное 100 или более дням, попытка вызова функции double_my_interval привела бы к ошибке!

Дело в том, что задаваемая по умолчанию точность типов данных INTERVAL не равна максимально возможной точности. Обычно вызывающая программа передает точность параметров программе PL/SQL, но с типами данных INTERVAL используется принятая по умолчанию точность 2. Для решения этой проблемы можно воспользоваться типами данных INTERVAL , явно объявляемыми без ограничения точности:

YMINTERVAL_UNCONSTRAINED — принимает любое значение типа INTERVAL YEAR TO MONTH без потери точности;
DSINTERVAL_UNCONSTRAINED — принимает любое значение типа INTERVAL DAY TO SECOND без потери точности.

Воспользовавшись типом DSINTERVAL_UNCONSTRAINED , приведенный выше пример можно переписать следующим образом:

Результат будет таким:

Обратите внимание на то, что тип данных DSINTERVAL_UNCONSTRAINED используется дважды: один раз для задания типа формального параметра функции double_my_interval , а второй — для задания типа возвращаемого значения. В результате эту функцию можно вызывать для любого значения типа INTERVAL DAY TO SECOND без потери точности или ошибок.

Все ниже рассмотренные функции работают с календарными типами данных.

Получение текущей даты и времени.

Чтобы получить текущую дату и время используется функция NOW ().

SELECT NOW ()
Результат: 2015-09-25 14:42:53

Для получения только текущей даты есть функция CURDATE ().

SELECT CURDATE ()
Результат: 2015-09-25

И функция CURTIME (), которая возвращает только текущее время:

SELECT CURTIME ()
Результат: 14:42:53

Функции CURDATE () и NOW () удобно использовать для добавления в базу данных записей, для которых требуется хранить дату добавления. Например, при добавлении статьи на сайт хорошо бы хранить ее дату публикации. Тогда запрос на добавление статьи в базу будет примерно таким:

Прибавление и вычитание дат и времени

Функция ADDDATE (date, INTERVAL value) прибавляет к дате date значение value и возвращает полученное значение. В качестве value могут выступать следующие значения:

SECOND — секунды
MINUTE — минуты
HOUR — часы
DAY — дни
WEEK — недели
MONTH — месяцы
QUARTER — кварталы
YEAR — годы

а также их комбинации:

MINUTE_SECOND — минуты и секунды
HOUR_SECONDчасы — минуты и секунды
HOUR_MINUTE — часы и минуты
DAY_SECOND — дни, часы, минуты и секунды
DAY_MINUTE — дни, часы и минуты
DAY_HOUR — дни и часы
YEAR_MONTH — года и месяцы.

Функция SUBDATE (date, INTERVAL value) производит вычитание значения value из даты date . Пример:

Функция PERIOD_ADD (period, n) прибавляет к значению period n месяцев. Значение период должно быть представлено в формате YYYYMM (например сентябрь 2015 года будет 201509). Пример:

SELECT PERIOD_ADD (201509, 4)
Результат: 201601

Функция TIMESTAMPADD (interval, n, date) прибавляет к дате date временной интервал n , значения которого задаются параметром interval . Возможные значения параметра interval:

FRAC_SECOND — микросекунды
SECOND — секунды
MINUTE — минуты
HOUR — часы
DAY — дни
WEEK — недели
MONTH — месяцы
QUARTER — кварталы
YEAR — годы

Функция SUBTIME (date, time) вычитает из даты date время time. Пример:

Вычисление интервала между датами

Функция TIMEDIFF (date1, date2) вычисляет разницу в часах, минутах и секундах между двумя датами date1 и date2 . Пример:

Функция DATEDIFF (date1, date2) вычисляет разницу в днях между двумя датами, при этом часы, минуты и секунды при указании дат игнорируются. Пример:

С помощью этой функции легко определить сколько дней прошло с даты публикации статьи:

SELECT DATEDIFF (CURDATE (), date_publication) FROM posts WHERE id_post = 1

Функция PERIOD_DIFF (period1, period2) вычисляет разницу в месяцах между двумя датами. Даты должны быть представлены в формате YYYYMM . Например, узнаем сколько месяцев прошло с января 2015 по сентябрь 2015:

SELECT PERIOD_DIFF (201509, 201501)
Результат: 9

Функция TIMESTAMPDIFF (interval, date1, date2) вычисляет разницу между датами date2 и date1 в единицах указанных в параметре interval . При этом interval может принимать следующие значения:

FRAC_SECOND — микросекунды
SECOND — секунды
MINUTE — минуты
HOUR — часы
DAY — дни
WEEK — недели
MONTH — месяцы
QUARTER — кварталы
YEAR — годы

Получение различных форматов даты и времени и другой информации

Функция DATE (datetime) возвращает дату, отсекая время. Пример:

Функция TIME (datetime) возвращает время, отсекая дату. Пример:

Функция TIMESTAMP (date) возвращает полный формат со временем даты date . Пример:

DAY (date) и DAYOFMONTH (date). Функции-синонимы, которые возвращают порядковый номер дня месяца. Пример:

Функции DAYNAME (date), DAYOFWEEK (date) и WEEKDAY (date). Первая функция возвращает название дня недели, вторая — номер дня недели (отсчет от 1 — воскресенье до 7 — суббота), третья также номер дня недели только другой отсчет(отсчет от 0 — понедельник, до 6 — воскресенье). Пример:

Функции WEEK (date) и WEEKOFYEAR (datetime). Обе функции возвращают номер недели в году, только у первой неделя начинается с воскресенья, а у второй с понедельника. Пример:

Функция MONTH (date) возвращает числовое значение месяца (от 1 до 12), а MONTHNAME (date) название месяца. Пример:

Функция QUARTER (date) возвращает номер квартала года (от 1 до 4). Пример:

Функция YEAR (date) возвращает значение года (от 1000 до 9999). Пример:

Функция DAYOFYEAR (date) возвращает порядковый номер дня в году (от 1 до 366). Прмиер:

Функция HOUR (datetime) возвращает значение часа (от 0 до 23). Пример:

Функция MINUTE (datetime) возвращает значение минут (от 0 до 59). Пример:

Функция SECOND (datetime) возвращает значение секунд (от 0 до 59). Пример:

Функция EXTRACT (type FROM date) возвращает часть даты date определяемую параметром type . Пример:

Взаимообратные функции TO_DAYS (date) и FROM_DAYS (n). Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату. Пример:

Взаимообратные функции UNIX_TIMESTAMP (date) и FROM_UNIXTIME (n). Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату. Пример:

Взаимообратные функции TIME_TO_SEC (time) и SEC_TO_TIME (n). Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время. Пример:

Функция MAKEDATE (year, n) принимает год year и номер дня в году n и преобразует их в дату. Пример:

Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:

Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.

Интервал времени

Синтаксис и реализация интервалов отличается на разных СУБД.

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() можно в любой вид и очередность при помощи подстановки ключей.

Ниже приведен список основных элементов форматирования для даты и времени:

Читайте также: