Oracle преобразовать в timestamp
The Oracle TO_TIMESTAMP function is useful if you need to work with timestamp data types. Learn how to use it with some examples in this article.
Invalid Argument Count
Calling the function without passing any arguments results in an error:
However, passing too many arguments doesn’t seem to cause any issues, as long as the first three are valid:
В БД Oracle для работы с датами предназначены 2 типа - DATE и TIMESTAMP .
Отдельно можно упомянуть INTERVAL - интервальный тип, который хранит диапазон между двумя датами.
Функция HEXTORAW
Преобразует шестнадцатеричную строку типа CHAR или VARCHAR2 в значение типа RAW . Синтаксис функции HEXTORAW :
The 'nlsparam' Argument
The optional 'nlsparam' argument specifies the language in which month and day names and abbreviations are provided. It takes the following form:
Note that the result is still returned according to the current session’s NLS_TIMESTAMP_FORMAT parameter. It’s just that the input was in a different language.
Here’s what happens when I change agosto to August while using the same 'nlsparam' value:
This happened because I didn’t provide the date in the language specified by the 'nlsparam' argument (Spanish).
Changing it to English solves this problem:
Ограничения неявного преобразования
Как видно из рис. 1, преобразование может выполняться только между определенными типами данных; PL/SQL не может преобразовать произвольный тип данных в любой другой. Более того, при некоторых неявных преобразованиях типов генерируются исключения. Возьмем следующую операцию присваивания:
В PL/SQL нельзя преобразовать строку «abc» в число, поэтому при выполнении приведенного кода инициируется исключение VALUE_ERROR . Вы сами должны позаботиться о том, чтобы значение, для которого PL/SQL выполняет преобразование типов, могло быть конвертировано без ошибок.
Тип TIMESTAMP
Тип TIMESTAMP является расширением типа DATE . Он также, как и тип DATE , позволяет хранить год, месяц, день, часы, минуты и секунды. Но пимимо всего этого в TIMESTAMP можно хранить доли секунды.
TIMESTAMP - максимально точный тип данных для хранения даты, точнее в ORACLE уже нет.
При описании колонки с типом TIMESTAMP можно указать точность, с которой будут храниться доли секунды. Это может быть число от 0 до 9. По умолчанию это значение равно 6.
Пример создания таблицы с колонкой типа TIMESTAMP :
Колонка logout_time может хранить доли секунды с точностью до 6 знаков после запятой, а колонка login_time - с точностью до 8 знаков.
Недостатки неявного преобразования
Неявное преобразование типов имеет ряд недостатков.
- PL/SQL относится к языкам со статической типизацией. Неявные преобразования означают потерю некоторых преимуществ статической типизации — таких, как ясность и надежность кода.
- Каждое неявное преобразование означает частичную потерю контроля над программой. Программист не выполняет его самостоятельно и никак им не управляет, а лишь предполагает, что оно будет выполнено и даст желаемый эффект. В этом есть элемент неопределенности — если компания Oracle изменит способ или условие выполнения преобразований, это может отразиться на программе.
- Неявное преобразование типов в PL/SQL зависит от контекста. Оно может работать в одной программе и не работать в другой, хотя на первый взгляд код кажется одинаковым. Кроме того, результат преобразования типов не всегда соответствует ожиданиями программиста.
- Программу легче читать и понять, если данные в ней преобразуются явно, поскольку при этом фиксируются различия между типами данных в разных таблицах или в таблице и коде. Исключая из программы скрытые действия, вы устраняете и потенциальную возможность ошибок.
Таким образом, в SQL и PL/SQL рекомендуется избегать неявного преобразования типов. Лучше пользоваться функциями, которые выполняют явное преобразование — это гарантирует, что результат преобразования будет точно соответствовать вашим ожиданиям.
Явное преобразование типов
Oracle предоставляет обширный набор функций и операторов, с помощью которых можно выполнить преобразование типов данных в SQL и PL/SQL. Их полный список приведен в табл. 1. Большая часть функций описывается в других главах книги (для них в последнем столбце указан номер главы).
Таблица 1. Функции преобразования типов в PL/SQL
Функция | Выполняемое преобразование |
ASCIISTR | Строку из любого набора символов в строку ASCII из набора символов базы данных |
CAST | Одно значение встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию. Этот способ может использоваться вместо традиционных функций (таких, как TO_DATE) |
CHARTOROWID | Строку в значение типа ROWID |
CONVERT | Строку из одного набора символов в другой |
FROM_TZ | В значение типа TIMESTAMP добавляет информацию о часовом поясе, преобразуя его тем самым в значение типа TIMESTAMP WITH TIME ZONE |
HEXTORAW | Значение из шестнадцатеричной системы в значение типа RAW |
MULTISET | Таблицу базы данных в коллекцию |
NUMTODSINTERVAL | Число (или числовое выражение) в литерал INTERVAL DAY TO SECOND |
NUMTOYMINTERVAL | Число (или числовое выражение) в литерал INTERVAL YEAR TO MONTH |
RAWTOHEX, RAWTONHEX | Значение типа RAW в шестнадцатеричный формат |
REFTOHEX | Значение типа REF в символьную строку, содержащую его шестнадцатеричное представление |
ROWIDTOCHAR, ROWIDTONCHAR | Двоичное значение типа ROWID в символьную строку |
TABLE | Коллекцию в таблицу базы данных; по своему действию обратна функции MULTISET |
THE | Значение столбца в строку виртуальной таблицы базы данных |
TO_BINARY_FLOAT | Число или строку в BINARY_FLOAT |
TO_BINARY_DOUBLE | Число или строку в BINARY_DOUBLE |
TO_CHAR, TO_NCHAR (числовая версия) | Число в строку (VARCHAR2 или NVARCHAR2 соответственно) |
TO_CHAR, TO_NCHAR (версия для дат) | Дату в строку |
TO_CHAR, TO_NCHAR (символьная версия) | Данные из набора символов базы данных в набор символов национального языка |
TO_BLOB | Значение типа RAW в BLOB |
TO_CLOB, TO_NCLOB | Значение типа VARCHAR2, NVARCHAR2 или NCLOB в CLOB (либо NCLOB) |
TO_DATE | Строку в дату |
TO_DSINTERVAL | Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в тип INTERVAL DAY TO SECOND |
TO_LOB | Значение типа LONG в LOB |
TO_MULTI_BYTE | Однобайтовые символы исходной строки в их многобайтовые эквиваленты (если это возможно) |
TO_NUMBER | Строку или число (например, BINARY_FLOAT) в NUMBER |
TO_RAW | Значение типа BLOB в RAW |
TO_SINGLE_BYTE | Многобайтовые символы исходной строки в соответствующие однобайтовые символы |
TO_TIMESTAMP | Символьную строку в значение типа TIMESTAMP |
TO_TIMESTAMP_TZ | Символьную строку в значение типа TO_TIMESTAMP_TZ |
TO_YMINTERVAL | Символьную строку типа CHAR, VARCHAR2, NCHAR или NVARCHAR2 в значение типа INTERVAL YEAR TO MONTH |
TRANSLATE . USING | Текст в набор символов, заданный для преобразования набора символов базы данных в национальный набор символов |
UNISTR | Строку произвольного набора символов в Юникод |
Months_between
Функция months_between возвращает разницу между датами в месяцах:
Default Format
In this example I omit the fmt argument:
When doing this, the argument must be in the default format of the TIMESTAMP data type, which is determined by the NLS_TIMESTAMP_FORMAT initialisation parameter.
Here’s an example of what happens when we pass a value that doesn’t conform to this format:
In this case, I passed a value that uses 24 hour time, but my NLS_TIMESTAMP_FORMAT parameter specifies a 12 hour clock with the AM/PM designator.
To fix this, I would either need to change my input, or change the value of the NLS_TIMESTAMP_FORMAT parameter.
Приведение строки к timestamp
Для приведения строки к типу timestamp используется фукнция TO_TIMESTAMP :
В запросе выше следует обратить внимание на то, как указывается точность долей секунды. ff3 будет сохранять точность до тысячных долей секунды, ff9 - до максимальных 9-и разрядов.
Форматы строк для приведения к датам очень разнообразны. Здесь приведены варианты, которые чаще всего понадобятся на практике. Ознакомиться со всеми форматами строк можно в докумениации.
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 . Будьте осторожны!
Syntax
The syntax goes like this:
The char argument can be any expression that evaluates to a character string of CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 data type.
The optional fmt specifies the format of char . If you omit fmt , then char must be in the default format of the TIMESTAMP data type, which is determined by the NLS_TIMESTAMP_FORMAT initialisation parameter (see How to Check the Date Format of your Oracle Session).
The optional 'nlsparam' argument specifies the language in which month and day names and abbreviations are provided. It takes the following form:
Null Arguments
Passing null results in null :
Example
Here’s a basic example to demonstrate:
The format of the result is determined by your session’s NLS_TIMESTAMP_FORMAT parameter. We can check the value of the NLS_TIMESTAMP_FORMAT parameter by querying the V$NLS_PARAMETERS view:
The default value of the NLS_TIMESTAMP_FORMAT parameter is derived from the NLS_TERRITORY parameter. In my case the NLS_TERRITORY parameter is AUSTRALIA .
Here’s what happens when I change the NLS_TERRITORY parameter to another territory, then call TO_TIMESTAMP() again:
This time the result is returned in a different format.
You can alternatively change the value of the NLS_TIMESTAMP_FORMAT parameter directly. This will change that parameter without affecting other parameters. See How to Change the Date Format in your Oracle Session for more information and examples.
Неявное преобразование типов
Обнаружив необходимость преобразования, PL/SQL пытается привести значение к нужному типу. Вероятно, вас удивит, насколько часто это делается. На рис. 1 показано, какие виды неявного преобразования типов выполняются PL/SQL.
Рис. 1. Неявные преобразования типов, выполняемые PL/SQL
Неявное преобразование типов осуществляется при задании в операторе или выражении литерального значения в правильном внутреннем формате, которое PL/SQL преобразует по мере необходимости. В следующем примере PL/SQL преобразует литеральную строку «125» в числовое значение 125 и присваивает его числовой переменной:
Неявное преобразование типов выполняется также при передаче программе параметров не того формата, который в ней используется. В следующей процедуре таким параметром является дата. Вызывая эту процедуру, вы передаете ей строку в формате ДД-МММ-ГГ, которая автоматически преобразуется в дату:
Неявное преобразование строки в дату выполняется в соответствии со спецификацией NLS_DATE_FORMAT . Проблема заключается в том, что в случае изменения NLS_DATE_FORMAT работа программы будет нарушена.
Can You Convert an Oracle TIMESTAMP to a CHAR?
Yes, you can, if you really need to.
You can use the TO_CHAR function, along with TRUNC, and specify the format.
This result is in a CHAR format. You can use a similar set of functions to convert your TIMESTAMP values to CHAR.
Функция CONVERT
Преобразует строку из одного набора символов в другой. Синтаксис функции:
Третий аргумент старый_набор_символов не является обязательным. Если он не задан, применяется набор символов, используемый в базе данных по умолчанию.
Функция CONVERT не переводит слова или фразы с одного языка на другой, а заменяет буквы или символы одного набора символов буквами или символами другого.
Provide a Default Value on Conversion Error
You also have the option of providing a value to return in the event that there’s an error while converting the argument to a TIMESTAMP type.
In this case I specified that null should be returned whenever there’s a conversion error.
By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.
However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.
Examples of the Oracle TO_TIMESTAMP Function
Here are some examples of the TO_TIMESTAMP function. I find that examples are the best way for me to learn about code, even with the explanation above.
Приведение строки к дате
Одна из часто встречающихся ситуаций - необходимость представить строку в виде типа данных DATE . Делается это при помощи функции to_date . Данная функция принимает 2 параметра - строку, содержащую в себе собственно дату, и строку, которая указывает, как нужно интерпретировать первый параметр, т.е. где в этой дате год, где месяц, число и т.п.
На самом деле, функция to_date может работать и без строки с форматом даты, а также с еще одним дополнительным параметром, который будет указывать формат языка, но мы будем рассматривать вариант с двумя параметрами. Более детально ознакомиться с функцией to_date можно вот здесь.
Как видно, строка, определяющая формат даты, имеет очень большое значение. В примере выше, мы получили две разные даты, изменив лишь их формат в функции to_date .
Приведение даты к строке
Чтобы отобразить дату в нужном нам формате, используется функция to_char .
Тип DATE
Тип DATE используется чаще всего, когда необходимо работать с датами в БД Oracle. Он позволяет хранить даты с точностью до секунд.
Некоторые БД, например MySQL, также имеют тип DATE, но там может храниться дата лишь с точностью до дня.
Функция CAST
Функция CAST является очень удобным и гибким механизмом преобразования данных. Она преобразует значение любого (или почти любого) встроенного типа данных или коллекции в другой встроенный тип данных или коллекцию, и скорее всего, будет знакома всем программистам с опытом работы на объектно-ориентированных языках.
С помощью функции CAST можно преобразовать неименованное выражение (число, дату, NULL и даже результат подзапроса) или именованную коллекцию (например, вложенную таблицу) в тип данных или именованную коллекцию совместимого типа. Допустимые преобразования между встроенными типами данных показаны на рис. 2. Необходимо соблюдать следующие правила:
- не допускается преобразование типов данных LONG , LONG RAW , любых типов данных LOB и типов, специфических для Oracle;
- обозначению « DATE » на рисунке соответствуют типы данных DATE , TIMESTAMP , TIMESTAMP WITH TIMEZONE , INTERVAL DAY TO SECOND и INTERVAL YEAR TO MONTH ;
- для преобразования именованной коллекции определенного типа в именованную коллекцию другого типа нужно, чтобы элементы обеих коллекций имели одинаковый тип;
Рис. 2. Преобразование встроенных типов данных PL/SQL
- тип UROWID не может быть преобразован в ROWID , если UROWID содержит значение ROWID индекс-таблицы.
Ниже приведен пример использования функции CAST для преобразования скалярных типов данных. Ее вызов может быть включен в SQL-команду:
Также возможен вызов в синтаксисе PL/SQL:
Намного более интересное применение CAST встречается при работе с коллекциями PL/SQL (вложенными таблицами и VARRAY), поскольку эта функция позволяет преобразовывать коллекцию из одного типа в другой. Кроме того, CAST может использоваться для работы (из инструкций SQL) с коллекцией, объявленной как переменная PL/SQL.
Обе темы подробно рассматриваются в главе 12, а следующий пример дает некоторое представление о синтаксисе и возможностях преобразования. Сначала мы создаем два типа вложенных таблиц и одну реляционную таблицу:
Далее пишется программа, которая связывает данные из таблицы favorite_authors с содержимым вложенной таблицы, объявленной и заполненной в другой программе. Рассмотрим следующий блок:
В строках 2 и 3 объявляется локальная вложенная таблица, заполняемая именами нескольких популярных авторов. В строках 7–11 с помощью оператора UNION объединяются строки таблиц favorite_authors и scifi_favorites. Для этого вложенная таблица scifi_favorites (локальная и не видимая для ядра SQL) преобразуется с использованием функции CAST в коллекцию типа names_t. Такое преобразование возможно благодаря совместимости их типов данных. После преобразования вызов команды TABLE сообщает ядру SQL, что вложенная таблица должна интерпретироваться как реляционная. На экран выводятся следующие результаты:
Does TO_TIMESTAMP Work The Same In Oracle 10g and 11g?
Yes, it works the same.
EXTRACT
Функция extract позволяет извлечь из даты определенные составные части, например получить только год, или только месяц и т.п.
Извлекаемые части имеют числовой тип данных, т.е. колонки year , month и day всего лишь числа.
Несмотря на то, что тип DATE хранит также время вплоть до секунд, получить часы, минуты или секунды нельзя:
В ответ мы получим ошибку ORA-30076: invalid extract field for extract source .
Но если использовать тип TIMESTAMP , то помимо года, месяца и дня с помощью функции EXTRACT можно по отдельности получить значение часов, минут и секунд:
Similar Functions
-
: This function converts a value into a DATE data type, which is similar, but has no time component.
You can find a full list of Oracle SQL functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
В ходе выполнения программы PL/SQL часто возникает необходимость преобразования данных из одного типа в другой. Преобразование может выполняться двумя способами:
- Неявно — поиск «оптимального варианта» поручается исполнительному ядру PL/SQL;
- Явно — преобразование выполняется вызовом функции или соответствующим оператором PL/SQL.
В этом разделе мы сначала разберемся, как в PL/SQL выполняются неявные преобразования, а затем перейдем к изучению функций и операторов явного преобразования.
Syntax
The syntax of the TO_TIMESTAMP function is:
Example 1
This example uses the TO_TIMESTAMP function with the default format.
TS_TEST |
16/SEP/15 08:10:14.000000000 AM |
The result is the same value, but converted to a TIMESTAMP data type.
Разница между датами
Если просто отнять от одной даты другую, то мы получим разницу между ними в днях. Также, к датам можно прибавлять и отнимать обычные числа, и Oracle будет оперировать ими как днями:
Функция ROWIDTOCHAR
Преобразует двоичное значение типа ROWID в строку типа VARCHAR2 . Синтаксис функции ROWIDTOCHAR :
Возвращаемая функцией строка имеет следующий формат:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке PL/SQL. Все четыре компонента задаются в формате Base64. Пример:
In Oracle Database, the TO_TIMESTAMP() function converts its argument to a value of TIMESTAMP data type.
Trunc
Функция trunc округляет дату до определенной точности. Под точностью в округлении даты следует понимать ту ее часть(день, месяц, год, час, минута), которая не будет приведена к единице, а будет такой же, как и в исходной дате.
Если не указывать формат округления, то trunc округлит до дней, т.е. колонки “2” и “3” будут содержать одинаковое значение.
Parameters
- input_string (mandatory): This is the input string, or the string to convert into the timestamp value.
- format_mask (optional): This string specifies the format of the input_string, as you can specify your input in many different ways. If this is not supplied, then the input_string must be in the same format as the NLS_TIMESTAMP_FORMAT parameter, which is your default format.
- nlsparam (optional): If this string is supplied, you can specify the language in which the month and day names and abbreviations are returned.
Purpose of the Oracle TO_TIMESTAMP Function
The purpose of the Oracle TO_TIMESTAMP function is to convert a string value into a timestamp value.
Now, a TIMESTAMP value is a type of date. It includes the date, the time, and a series of decimal places to indicate fractional seconds.
Converting a string to a TIMESTAMP value is done using the TO_TIMESTAMP function.
SYSTIMESTAMP
Данная функция работает так же, как и SYSDATE , только она возвращает текущую дату в формате TIMESTAMP :
Функция CHARTOROWID
Преобразует строку типа CHAR или VARCHAR2 в значение типа ROWID . Синтаксис функции:
Для успешного преобразования функцией CHARTOROWID строка должна состоять из 18 символов в формате:
где ОООООО — номер объекта данных, ФФФ — относительный номер файла базы данных, ББББББ — номер блока в файле, а ССС — номер строки в блоке. Все четыре компонента задаются в формате Base64. Если исходная строка не соответствует этому формату, инициируется исключение VALUE_ERROR .
Функция SYSDATE
Данная функция возвращает текущую дату. В зависимости от того, когда следующий запрос выполнится, значение SYSDATE будет всегда разным.
ADD_MONTHS
Функция add_months добавляет указанное количество месяцев к дате. Для того, чтобы отнять месяцы от даты, нужно передать в качестве второго параметра отрицательное число:
Example 3
This example uses the TO_TIMESTAMP function, but we specify a different format.
TS_TEST |
08/DEC/15 10:04:00.000000000 AM |
The result is the same value, but converted to a TIMESTAMP data type.
Example 2
This example uses the TO_TIMESTAMP function, but we specify a format.
TS_TEST |
12/SEP/15 04:32:20.000000000 PM |
The result is the same value, but converted to a TIMESTAMP data type.
What Format Do I Need To Supply For Oracle TO_TIMESTAMP?
The default format of the TO_TIMESTAMP function is the value of NLS_TIMESTAMP_FORMAT, which depends on your Oracle database.
The format can be something like this: ‘YYYY-MM-DD HH:MI:SS.FF’
Parameter | Explanation |
---|---|
YYYY | Four digit year |
MM | Month (01-12, where JAN = 01) |
MON | Abbreviated name of month |
MONTH | Name of month, padded with blanks to length of 9 characters |
DD | Day of month (1-31) |
HH | Hour of day (1-12) |
HH12 | Hour of day (1-12) |
HH24 | Hour of day (0-23) |
MI | Minute (0-59) |
SS | Second (0-59) |
Функция RAWTOHEX
Преобразует значение типа RAW в шестнадцатеричную строку типа VARCHAR2 . Синтаксис функции RAWTOHEX :
Функция RAWTOHEX всегда возвращает строку переменной длины, хотя обратная ей перегруженная функция HEXTORAW поддерживает оба типа строк.
Читайте также: