Oracle тип данных interval
Обновлено: 04.07.2024
Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:
Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.
Интервал времени
Синтаксис и реализация интервалов отличается на разных СУБД.
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() можно в любой вид и очередность при помощи подстановки ключей.
Ниже приведен список основных элементов форматирования для даты и времени:
Команда INTERVAL позволяет прибавлять к дате и отнимать от нее определенные промежутки времени.
Синтаксис
После команды INTERVAL можно указывать определенную часть даты (день, месяц или год и тп), к примеру, так INTERVAL 1 DAY или INTERVAL 3 MONTH, или сразу несколько частей.
В этом случае значения даты берутся в кавычки, пример: давайте прибавим 1 год и 3 месяца - это будет выглядеть так: INTERVAL '1-3' YEAR_MONTH.
Мы указываем, что хотим год и месяц (вот так: YEAR_MONTH), и перед этим в кавычках пишем сначала значение для года, потом значение для месяца. Между ними ставим разделитель (он может быть абсолютно любым).
Смотрите список возможных комбинаций (разделители указаны для примера, они могут быть любыми):
| Значение | Ожидаемый формат |
|---|---|
| SECOND | секунды |
| MINUTE | минуты |
| HOUR | часы |
| DAY | дни |
| MONTH | месяцы |
| YEAR | года |
| MINUTE_SECOND | "минуты:секунды" |
| HOUR_MINUTE | "часы:минуты" |
| DAY_HOUR | "дни часы" |
| YEAR_MONTH | "года-месяцы" |
| HOUR_SECOND | "часы:минуты:секунды" |
| DAY_MINUTE | "дни часы:минуты" |
| DAY_SECOND | "дни часы:минуты:секунды" |
Примеры
Все примеры будут по этой таблице workers, если не сказано иное:
| id айди | name имя | date дата |
|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 |
Пример
В данном примере к дате прибавляется 1 день:
SQL запрос выберет следующие строки:
| id айди | name имя | date дата | new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2010-03- 02 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2011-05- 03 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2012-06- 04 14:03:04 |
Пример
В данном примере от даты отнимается 1 день:
SQL запрос выберет следующие строки:
| id айди | name имя | date дата | new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2010- 02-28 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2011-05- 01 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2012-06- 02 14:03:04 |
Пример
В данном примере к дате прибавляется 1 год и 2 месяца:
SQL запрос выберет следующие строки:
| id айди | name имя | date дата | new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2011-05 -01 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2012-06 -02 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2013-07 -03 14:03:04 |
Разделитель в запросе не имеет значения. Он может быть любым символом:
Можно использовать даже несколько символов:
Можно использовать и пробел:
Пример
В данном примере к дате прибавляется 1 год, 2 месяца и 1 день:
SQL запрос выберет следующие строки:
| id айди | name имя | date дата | new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2011-05-02 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2012-06-03 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2013-07-04 14:03:04 |
Запрос можно переписать таким образом:
Пример
В данном примере к дате прибавляется 1 год, отнимаются 2 месяца и прибавляется 1 день:
До 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 — новый элемент маски формата данных FF7 предназначен для задания долей секунд. Если вы укажете точность меньше той что хранится в БД, 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. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
Типы, рассматривавшиеся до настоящего момента, представляют определенные моменты времени. Типы данных INTERVAL , появившиеся в Oracle9i, предназначены для сохранения и обработки временных промежутков. Чтобы получить более полное представление о них, вспомним, с какими значениями даты/времени мы сталкиваемся в повседневной жизни:
Oracle поддерживает два типа данных INTERVAL . Оба типа были введены в Oracle9i, и оба соответствуют стандарту ISO SQL:
Объявление интервальных переменных в PL/SQL
По сравнению с другими объявлениями переменных PL/SQL синтаксис объявлений переменных обоих типов INTERVAL несколько необычен. Помимо того, что имена этих типов состоят из нескольких слов, для них задается не одно, а два значения, определяющих точность:
Здесь имя_переменной — имя объявляемой переменной INTERVAL ; точность_лет — количество цифр (от 0 до 4), выделенное для представления количества лет (по умолчанию 2); точность_дней — количество цифр (от 0 до 9), выделенное для представления количества дней (по умолчанию 2); точность_долей_секунды — количество цифр (от 0 до 9), выделенное для представления количества долей секунды (по умолчанию 6).
О точности значений интервалов, как правило, можно не беспокоиться. Значения типа INTERVAL YEAR TO MONTH всегда нормализуются таким образом, что количество месяцев лежит в диапазоне от 0 до 11. Фактически Oracle не позволяет задать месяц значением больше 11; интервал в 1 год и 13 месяцев должен быть выражен как 2 года и 1 месяц. Значение параметра точность_лет устанавливает максимальный размер интервала типа INTERVAL YEAR TO MONTH . Аналогичным образом значение параметра точность_дней устанавливает максимальный размер интервала типа INTERVAL DAY TO SECOND .
Точность для часов, минут и секунд для значения типа INTERVAL DAY TO SECOND не нужно задавать по той же причине, по которой не задается точность для месяцев значения типа INTERVAL YEAR TO MONTH . Интервалы INTERVAL DAY TO SECOND всегда нормализуются таким образом, что значения часов, минут и секунд находятся в естественных диапазонах: 0–23 часа, 0–59 минут и 0–59 секунд (за исключением долей секунды).
Доли секунды указываются потому, что значения типа INTERVAL DAY TO SECOND могут определять интервалы с указанной точностью до долей секунды. Значения типа INTERVAL YEAR TO MONTH не могут содержать долей месяца, и последние для них не задаются.
Когда используются типы INTERVAL в PL/SQL
Используйте типы данных INTERVAL во всех случаях, когда вам потребуется обрабатывать промежутки времени. В этом разделе приведены два примера; хочется верить, что они вызовут у вас интерес и помогут представить, как эти типы данных могли бы использоваться в создаваемых вами системах.
Вычисление разности между двумя значениями даты/времени
Типы INTERVAL удобно использовать для вычисления разности между двумя значениями даты/времени. В следующем примере вычисляется срок работы сотрудника:
Непосредственное вычисление количества лет и месяцев работы выполняется в следующей строке:
Здесь YEAR TO MONTH — часть синтаксиса выражения, возвращающего интервал. Подробнее о нем рассказывается далее в этой главе. Как видите, вычисление продолжительности интервала сводится к простому вычитанию одной даты из другой. Без типа данных INTERVAL нам пришлось бы программировать вычисления самостоятельно:
Решение, в котором не используется тип данных INTERVAL , оказывается более сложным как для программирования, так и для понимания кода.
Тип INTERVAL YEAR TO MONTH выполняет округление значений, и вы должны знать о возможных последствиях этой операции. За подробностями обращайтесь к разделу «Арифметические операции над значениями даты/времени».
Обозначение периода времени
В этом примере анализируется работа конвейерной сборки. Важной метрикой эффективности является время, необходимое для сборки каждого продукта. Сокращение этого интервала повышает эффективность работы конвейера, поэтому начальство желает постоянно контролировать его продолжительность. В нашем примере каждому продукту присваивается контрольный номер, используемый для его идентификации в процессе сборки. Информация хранится в следующей таблице:
Также нам понадобится функция PL/SQL, возвращающая время сборки для заданного идентификатора tracking_id . Значение вычисляется вычитанием текущего времени из времени начала сборки. Арифметические операции с датами более подробно рассматриваются позднее в этой главе. Функция вычисления времени сборки:
При передаче интервалов программам PL/SQL и из них необходимо использовать ключевое слово UNCONSTRAINED (см. далее раздел «Типы данных INTERVAL без ограничений»). Хранение времени сборки в таблице упрощает анализ данных. Мы можем легко определить минимальное, максимальное и среднее время сборки при помощи простых функций SQL, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.
Читайте также: