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 , появившиеся в 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, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.
Oracle реализует набор функций для работы со значениями типа даты/времени. Мы не будем подробно рассматривать все функции, но сводка в табл. 1 познакомит Вас с доступными возможностями. Если какие-то функции вас заинтересуют, обращайтесь за подробным описанием к справочнику Oracle SQL Reference.
Избегайте использования традиционных функций Oracle, обрабатывающих значения типа DATE , при работе с новыми типами данных TIMESTAMP . Вместо них следует по возможности использовать новые функции для типов INTERVAL . А DATE -функции должны использоваться только для обработки значений типа DATE .
Многие из приведенных в табл. 1 функций (в том числе ADD_MONTHS ) получают значения типа DATE . При использовании таких функций с новыми типами данных TIMESTAMP могут возникнуть проблемы. Хотя любой из этих функций можно передать значение типа TIMESTAMP , Oracle неявно преобразует его к типу DATE , и только тогда функция выполнит свою задачу, например:
В этом примере переменная ts содержит значение типа TIMESTAMP WITH TIME ZONE . Это значение неявно преобразуется в DATE при передаче LAST_DAY . Поскольку в типе DATE не сохраняются ни дробные части секунд, ни смещение часового пояса, эти части значения ts попросту отбрасываются. Результат LAST_DAY снова присваивается ts , что приводит к выполнению второго неявного преобразования — на этот раз DATE преобразуется в TIMESTAMP WITH TIME ZONE . Второе преобразование получает часовой пояс сеанса, поэтому в смещении часового пояса в итоговом значении мы видим ?05:00.
Очень важно понимать эти преобразования. и избегать их. Несомненно, вы представляете, какие коварные ошибки могут появиться в программе из-за неосторожного использования функций DATE со значениями TIMESTAMP . Честно говоря, я не представляю, почему в Oracle встроенные функции DATE не были перегружены для нормальной работы с TIMESTAMP . Будьте осторожны!
When subtracting timestamps the return value is an interval data-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer.
The following would work, but it's not very pretty:
Is there a more elegant method in SQL or PL/SQL?
9 Answers 9
The idea is to convert the interval value into days by times 86400 (= 24*60*60). Then extract the 'day' value which is actually second value we wanted.
@ŠtefanOravec I did a quick test. The biggest interval I can get is 11574 days in Oracle 11.2.0.4 as below: > select extract(day from (systimestamp - (systimestamp - 11574))*86400) seconds from dual; SECONDS ---------- 999993600I hope this help:
I've found this to work. Apparently, if you do arithmetics with timestamps they are converted to some internal datatype that, when substracted from each other, returns the interval as a number.
Easy? Yes. Elegant? No. Gets the work done? Oh yeah.
Unfortunately, I don't think that there is an alternative (or more elegant) way of calculating total seconds from an interval type in pl/sql. As this article mentions:
therefore extracting day, hour etc from the interval and multiplying them with corresponding values seems like the only way.
26.1k 15 15 gold badges 76 76 silver badges 90 90 bronze badges 10.7k 5 5 gold badges 33 33 silver badges 45 45 bronze badgesBased on zep's answer, I wrapped things up into a function for your convenience:
Use following query:
7,296 7 7 gold badges 31 31 silver badges 37 37 bronze badges 1,219 1 1 gold badge 9 9 silver badges 4 4 bronze badgesSimilar to @Zhaoping Lu answer but directly extracting seconds instead of getting them from the number of days.
(worked on PostgresSQL 9.6.1)
A shorter method to convert timestamp to nanoseconds.
A method to convert nanoseconds to timestamp.
As expected, above methods' results are not affected by time zones.
A shorter method to convert interval to nanoseconds.
A method to convert nanoseconds to interval.
Replace 1000000000 by 1000, for example, if you'd like to work with milliseconds instead of nanoseconds.
I've tried some of posted methods, but a got the exception "ORA-01873: the leading precision of the interval is too smalll" when multiplying the interval by 86400, so I've decided do post the methods that works for me.
Читайте также: