Oracle преобразовать в timestamp

Обновлено: 30.06.2024

1 ответ

Как преобразовать шестнадцатеричное число в десятичное (и обратно) с помощью Oracle SQL?

Таким образом, у меня есть некоторые Oracle TIMESTAMP в дампе SQL из моей базы данных Oracle. И я хочу импортировать это в базу данных дерби. В Oracle оператор SQL выглядит следующим образом: Insert into TABLE_NAME (COL1, COL2, COL3) values ( 'blah', to_timestamp('17-MAR-11.

Вы преобразуете свой номер в строку с помощью to_char. Затем вы преобразуете эту строку в timestamp с помощью метки to_timestamp. Время автоматически будет полночь.

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

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

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

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

По каким-то причинам не получается правильно преобразовать TIMESTAMP WITH TIMEZONE из одной временной зоны в другую, пример:

В данном случае в результате преобразования часовой пояс вообще сбросился, не смотря на успешное выполнение преобразования.

Почему в первом примере результат CURRENT_TIMESTAMP отстает на час от реального, которое должно быть равно 15:15:33 , а не 14:15:33 ?
Почему во втором примере, не смотря на успешное выполнение команды, конвертация не происходит? Может какие-то параметры базы данных не настроены, или я не понимаю как AT TIME ZONE работает? Наименования временных зон я брал из V$TIMEZONE_NAMES .

Версия БД: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

UPD: Данные из nls_session_parameters

50.3k 152 152 золотых знака 54 54 серебряных знака 211 211 бронзовых знаков

Уточните, в первом примере - SYSTIMESTAMP показывает время по-NY, -4 и +3 = 7 часов разницы, вроде правильно, но вы не жалуетесь, что в Нью Йорке часы тоже отстают. @0xdb, я на это не обратил внимание, если честно, получается оно на час в принципе отстает Ну тогда с 1-м вопросом понятно, системноое время установлено не верно. Ко 2-му вопросу: Преобразование производит клиент. Каким вы пользуетесь? Какие NLS настройки, переменные окружения, что возвращает nls_session_parameters? Можете попробовать sqlpus прямо на машине, где база установлена? @0xdb 1 действительно вылечилось установкой системного времени. Со вторым получилось, что SQL Developer производит правильно преобразования, а dbForge\плагин к IDEA нет. Думаю, можете написать это как ответ и я его приму тогда. Параметры сессии тоже скинул в вопрос. Рад, что получилось. Напишу ответ вечером. В вопросе, как я понял - dbForge. SQL Developer тоже не безгрешен, а sqlpus пока замечен не был.

К 1-ой части вопроса

SYSTIMESTAMP возвращает текущее время и часовой пояс операционной системы машины на которой установлен сервер БД.

CURRENT_TIMESTAMP возвращает эти же данные учитывая часовой пояс сессии на клиенте, который определяется клиентом из окружения (установки региона на ОС, переменные) в котором он запущен и может быть изменён непосредственно: setenv ORA_SDTZ="Europe/Moscow" или alter session set time_zone='+03:00' .

Посмотреть часовой пояс сессии можно так:

В примере разница между Москвой и Нью-Йорком составляет -4 и +3 = 7 часов, что соответствует действительности. Значит время, которое возвращает SYSTIMESTAMP тоже с отставанием, т.е. системное время установленно не верно.

Ко 2-ой части вопроса

Преобразование даты и времени производится на стороне клиента и зависит от его NLS настроек, часового пояса (см. выше), которые можно посмотреть:

Некоторые клиенты, как в данном примере с dbForge\IDEA "потерялся" часовой пояс, или sqlplus не учитывает изменения часового пояса в России с октября 2014 года, выполняют преобразование не всегда верно. Поэтому при сомнении имеет смысл выполнить запрос на различных клиентах.

На заметку не в рамках вопроса

Oracle рекомендует устанавливать DBTIMEZONE на UTC, если не используются по каким-то специфическим соображениям тип данных TIMESTAMP WITH LOCAL TIME ZONE .

Почему в первом примере результат CURRENT_TIMESTAMP отстает на час от реального, которое должно быть равно 15:15:33 , а не 14:15:33 ?
Почему во втором примере, не смотря на успешное выполнение команды, конвертация не происходит? Может какие-то параметры базы данных не настроены, или я не понимаю как AT TIME ZONE работает? Наименования временных зон я брал из V$TIMEZONE_NAMES .