Oracle дата в формате iso 8601
Много было сказано (и написано на SO) по частям этой темы, но не в полной, исчерпывающей форме, поэтому у нас может быть одно "окончательное, всеобъемлющее" решение для всех.
У меня есть база данных Oracle, в которой я храню дату + время + часовой пояс глобальных событий, поэтому исходный TZ должен быть сохранен и доставлен на клиентскую сторону по запросу. В идеале он мог бы нормально работать, используя стандартный формат «T» ISO 8601, который можно удобно сохранить в Oracle, используя тип столбца «TIMESTAMP WITH TIME ZONE» («TSTZ»).
Что-то вроде '2013-01-02T03: 04: 05.060708 + 09: 00'
Проблема в том, что Java не поддерживает ISO 8601 (или любой другой тип данных date + time + nano + tz), и ситуация еще хуже, потому что драйвер Oracle JDBC (ojdbc6.jar) имеет еще меньше поддержки TSTZ (в отличие от Сама база данных Oracle, где она хорошо поддерживается).
В частности, вот чего я не должен или не могу:
- Любое сопоставление из TSTZ в Java Date, Time, Timestamp (например, через вызовы JDBC getTimestamp ()) не будет работать, потому что я теряю TZ.
- Драйвер Oracle JDBC не предоставляет никакого метода для сопоставления TSTZ с объектом java Calendar (это может быть решением, но его там нет)
- JDBC getString () может работать, но драйвер Oracle JDBC возвращает строку в формате
'2013-01-02 03: 04: 05.060708 +9: 00', что не соответствует ISO 8601 (без «T», без завершающего 0 в TZ , так далее.). Более того, этот формат жестко запрограммирован (!) Внутри реализации драйвера Oracle JDBC, который также игнорирует настройки локали JVM и настройки форматирования сеанса Oracle (т.е. игнорирует переменную сеанса NLS_TIMESTAMP_TZ_FORMAT). - JDBC getObject () или getTIMESTAMPTZ () оба возвращают объект Oracle TIMESTAMPTZ, который практически бесполезен, поскольку не имеет никакого преобразования в Calendar (только Date, Time и Timestamp), поэтому мы снова теряем информацию TZ.
Итак, вот варианты, которые у меня остались:
Используйте преобразование Oracle DB "toString": "SELECT TO_CHAR (tstz . ) EVENT_TIME . ". Это работает нормально, но имеет два основных недостатка:
- Каждый SELECT теперь должен включать вызов TO_CHAR, который сложно запомнить и написать.
- Теперь каждый SELECT должен добавить «псевдоним» столбца EVENT_TIME (необходимый, например, для автоматической сериализации результата в Json)
.
Используйте Java-класс Oracle TIMESTAMPTZ и извлеките соответствующее значение вручную из его внутренней (документированной) структуры массива байтов (т.е. реализуйте мой собственный метод toString (), который Oracle забыл реализовать там). Это рискованно, если Oracle изменяет внутреннюю структуру (что маловероятно) и требует относительно сложных функций для реализации и поддержки.
Я надеюсь, что есть 4-й, отличный вариант, но, просмотрев всю сеть и ТАК, я не вижу ни одного.
ОБНОВИТЬ
Ниже было дано множество идей, но похоже, что нет правильного способа сделать это. Лично я считаю, что использование метода №1 является самым коротким и наиболее читаемым (и обеспечивает приличную производительность без потери субмиллисекунд или возможности запросов на основе времени SQL ).
International Standards Organization (ISO) standard 8601 describes an internationally accepted way to represent dates, times, and durations. Oracle Database supports the most common ISO 8601 formats as proper Oracle SQL date, time, and interval (duration) values. The formats that are supported are essentially those that are numeric-only, language-neutral, and unambiguous.
(Simple Oracle Document Access (SODA) does not support durations.)
Oracle Database Syntax for ISO Dates and Times
This is the syntax that Oracle Database supports for ISO dates and times:
Date (only): YYYY - MM - DD
Date with time: YYYY - MM - DD T hh : mm : ss [ . s [ s [ s [ s [ s [ s ]]]]][ Z |( + | - ) hh : mm ]
YYYY specifies the year , as four decimal digits.
MM specifies the month , as two decimal digits, 00 to 12 .
DD specifies the day , as two decimal digits, 00 to 31 .
hh specifies the hour , as two decimal digits, 00 to 23 .
mm specifies the minutes , as two decimal digits, 00 to 59 .
ss [ . s [ s [ s [ s [ s ]]]]] specifies the seconds , as two decimal digits, 00 to 59 , optionally followed by a decimal point and 1 to 6 decimal digits (representing the fractional part of a second).
Z specifies UTC time (time zone 0). (It can also be specified by +00:00 , but not by –00:00 .)
( + | - )hh : mm specifies the time-zone as difference from UTC . (One of + or – is required.)
For a time value, the time-zone part is optional. If it is absent then UTC time is assumed.
No other ISO 8601 date-time syntax is supported. In particular:
Negative dates (dates prior to year 1 BCE), which begin with a hyphen (e.g. – 2018–10–26T21:32:52 ), are not supported.
Hyphen and colon separators are required: so-called “basic” format, YYYYMMDDThhmmss , is not supported.
Ordinal dates (year plus day of year, calendar week plus day number) are not supported.
Using more than four digits for the year is not supported.
Supported dates and times include the following:
Unsupported dates and times include the following:
Oracle Database Syntax for ISO Durations
Oracle Database supports ISO durations, but Simple Oracle Document Access (SODA) does not support them.
There are two supported Oracle Database syntaxes for ISO durations, the ds_iso_format specified for SQL function to_dsinterval and the ym_iso_format specified for SQL function to_yminterval . ( to_dsinterval returns an instance of SQL type INTERVAL DAY TO SECOND , and to_yminterval returns an instance of type INTERVAL YEAR TO MONTH .)
These formats are used for data types daysecondInterval and yearmonthInterval , respectively, which Oracle has added to the JSON language.
ds_iso_format:
P dD T hHmMsS , where d , h , m , and s are digit sequences for the number of days, hours, minutes, and seconds, respectively. For example: "P0DT06H23M34S" .
s can also be an integer-part digit sequence followed by a decimal point and a fractional-part digit sequence. For example: P1DT6H23M3.141593S .
Any sequence whose value would be zero is omitted, along with its designator. For example: "PT3M3.141593S" . However, if all sequences would have zero values then the syntax is "P0D" .
ym_iso_format
P yYmM , where y is a digit sequence for the number of years and m is a digit sequence for the number of months. For example: "P7Y8M" .
If the number of years or months is zero then it and its designator are omitted. Examples: "P7Y" , "P8M" . However, if there are zero years and zero months then the syntax is "P0Y" .
Want to improve this question? Add details and clarify the problem by editing this post.
Closed 6 years ago .
Note: I only have read access to this database.
I need to convert an Oracle 9 date type from d/mm/yyyy hh:mm:ss format to the iso8601 standard. Like the example below:
4/22/2015 12:02:56 AM => 2015-04-22T04:02:56.000Z
Below is a a query that will convert an Oracle date type to a iso8601-formatted string. I need an is8601 formatted date.
Thanks for any and all help.
EDIT: The end goal is to grab these dates and insert them as datetimes into a postgres database that powers a rails app.
I don't understand datetime type. I know DATE, TIMESTAMP , INTERVAL data types. In this manual Oracle uses 'datetime' for a DATE or one of the TIMESTAMP data types , but there is not somethings like a DATETIME data type. if you have a column of DATE or TIMESTAMP data type, then they dont have a format. So what kind of data type does your source have?
The string '4/22/2015 12:02:56 AM' does not represent a datetime in the 'd/mm/yyyy hh:mm:ss' format. Ther is no month represented by 22.
ok, then you want to convert them in strings that represents the date in an iso8601 format. this strings you send to your postgres session that will transform them to the appropriate postgres datetime data type. So you can use one of the given answers?
if you do a 'select COLNAME from TABLENAME' on the DATE column COLNAME of table TABLENAME an this returns values like '4/22/2015 12:02:56 AM' this does not mean that there are DATE values of this format stored in the table but that your default conversion for the DATE data type to the string data type in your session is 'm/DD/YYYY HH:MI:SS AM'. It does not mean that they are stored in this format.
both Oracle and PostgreSQL have a DATE and TIMESTAMP data types. But you cannot read it directly in an Oracle session and write it in a PostgreSQL session. Because there internal representation of this data is completely different. You have to convert it to a string when reading it in the Oracle session and convert it from a string when writing in the postgres session.
Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.
Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.
Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.
Функция to_date и форматы даты
Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.
Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски 'DDD', которая возвращает номер дня в году:
Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.
Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.
Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?
Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида 'DD.MM.YY' или 'DD-MON-YYYY' мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. '2011'.
Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:
В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку '03.02.2011 00:00:00', а запрос №5 — '03.02.2011 00:30:00'.
Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.
Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?
На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.
Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также " и удвоенную одинарную кавычку ''. Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:
Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.
Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:
То выполнение прошло бы успешно, и он вернул строку '09/15/2011 23:00:11'. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка '15.09.11', далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:
Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка '15.09.11' не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.
Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.
- Уровень БД: Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
- Уровень экземпляра: Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
- Уровень сессии: Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса:
Логично предположить, что преобразование строки '11.09.11' в дату пройдет успешно, а строки '11.SEP.11' – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в 'DD-MON-RR'). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.
Попробуем другой пример:
Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка '11.09.11' не удовлетворяет шаблону. Возможно, это мистика?
Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' | 'MON' and 'MONTH' |
'MON | 'MONTH' |
'MONTH' | 'MON' |
'YY' | 'YYYY' |
'RR' | 'RRRR' |
Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:
Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?
Как несложно предположить, в запросе №16 под '11' я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.
В запросе №17 под '99' я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.
Рассмотрим, как работают эти элементы форматирования более детально:
- If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
- If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
- If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
- If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
Поэтому запрос №19 вернет 1950 год в обоих случаях.
Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:
Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.
Какой из запросов не выполнится?
Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.
В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?
Каждый раз, когда сессия с форматом даты по умолчанию 'DD.MON.RR' будет производить вставку с указанием только значения первого столбца будет возникать ошибка.
Особенности отображения дат в различных приложениях
Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.
Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.
Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:
Проверим, какие параметры для отображения дат использует sqlplus.
Рис. 1. Результат выполнения запроса №25 в sqlplus.
Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.
Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.
Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.
Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.
Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.
Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.
Результаты выполнения запроса представлены на рисунках ниже.
Рис. 4. Результат выполнения запроса №26 в sqlplus.
Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.
Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.
Заключение
В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.
Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.
Все DATE имеют временной компонент; однако обычно принято хранить даты, которые не обязательно должны включать информацию о времени с часами / минутами / секундами, установленными на ноль (т.е. полночь).
Преобразуйте его из строкового литерала с помощью TO_DATE() :
(Более подробную информацию о моделях формата даты можно найти в документации Oracle.)
(Если вы конвертируете специфические для языка термины, такие как имена месяцев, то хорошей практикой является включение nlsparam параметра nlsparam в функцию TO_DATE() и указание языка, который следует ожидать.)
Создание дат с помощью компонента времени
Преобразуйте его из строкового литерала с помощью TO_DATE() :
Oracle будет неявным образом использовать TIMESTAMP для DATE при хранении в столбце DATE таблицы; однако вы можете явно CAST() значение CAST() для DATE :
Формат даты
DATE которая не хранится в таблице (т.е. сгенерирована SYSDATE и имеет тип 13 при использовании команды DUMP() ) имеет 8 байтов и имеет структуру (числа справа являются внутренним представлением 2012-11-26 16:41:09 ):
DATE который хранится в таблице («тип 12» при использовании команды DUMP() ) имеет 7 байтов и имеет структуру (числа справа являются внутренним представлением 2012-11-26 16:41:09 ):
Если вы хотите, чтобы дата имела определенный формат, вам нужно будет преобразовать ее в то, что имеет формат (т. Е. Строку). Клиент SQL может косвенно выполнять это или вы можете явно преобразовать значение в строку с использованием TO_CHAR( date, format_model, nls_params ) .
Преобразование дат в строку
(Примечание: если модель формата не NLS_DATE_FORMAT параметр сеанса NLS_DATE_FORMAT будет использоваться в качестве модели формата по умолчанию , это может быть различным для каждого сеанса, поэтому не следует полагаться. Хорошая практика всегда указывать модель формата.)
Установка модели формата даты по умолчанию
Когда Oracle неявно преобразует из DATE в строку или наоборот (или когда TO_CHAR() или TO_DATE() явно вызываются без модели формата), параметр сеанса NLS_DATE_FORMAT будет использоваться в качестве модели формата при преобразовании. Если литерал не соответствует модели формата, будет создано исключение.
Вы можете просмотреть этот параметр, используя:
Вы можете установить это значение в текущем сеансе, используя:
(Примечание: это не изменяет значение для других пользователей.)
Если вы полагаетесь на NLS_DATE_FORMAT для предоставления маски формата в TO_DATE() или TO_CHAR() вы не должны удивляться, когда ваши запросы ломаются, если это значение когда-либо изменяется.
Изменение того, как SQL / Plus или SQL Developer отображают даты
Когда SQL / Plus или SQL Developer отображают даты, они будут выполнять неявное преобразование в строку с использованием модели формата даты по умолчанию (см. Пример « Установка образца модели даты по умолчанию» ).
Вы можете изменить способ отображения даты, изменив параметр NLS_DATE_FORMAT .
Арифметика даты - разница между датами в днях, часах, минутах и / или секундах
В оракуле разница (в днях и / или их фракциях) между двумя DATE s может быть найдена с помощью вычитания:
Выводит количество дней между двумя датами:
Выводит долю дней между двумя датами:
Разницу в часах, минутах или секундах можно найти, умножив это число на 24 , 24*60 или 24*60*60 соответственно.
Предыдущий пример можно изменить, чтобы получить дни, часы, минуты и секунды между двумя датами, используя:
(Примечание: TRUNC() используется вместо FLOOR() чтобы правильно обрабатывать отрицательные отличия.)
Предыдущий пример также может быть решен путем преобразования числовой разности в интервал, используя NUMTODSINTERVAL() :
Арифметика даты - разница между датами в месяцах или годах
Разницу в месяцах между двумя датами можно найти с помощью MONTHS_BETWEEN( date1, date2 ) :
Если разница включает частичные месяцы, тогда она вернет часть месяца на основании 31 дня в каждом месяце:
Из-за MONTHS_BETWEEN предполагающего 31 день в месяц, когда может быть меньше дней в месяц, это может привести к разным значениям различий, охватывающих границы между месяцами.
Разницу в годах можно найти, разделив разницу в месяц на 12.
Извлечение года, месяца, дня, часа, минут или вторых компонентов даты
Компоненты года, месяца или дня типа данных DATE можно найти, используя EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )
Компоненты времени (час, минута или секунда) могут быть найдены либо:
- Использование CAST( datevalue AS TIMESTAMP ) для преобразования DATE в TIMESTAMP а затем с помощью EXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ) ; или же
- Использование TO_CHAR( datevalue, format_model ) для получения значения в виде строки.
Временные зоны и летнее время
Тип данных DATE не обрабатывает часовые пояса или изменения в летнее время.
- используйте тип данных TIMESTAMP WITH TIME ZONE ; или же
- обрабатывать изменения в логике вашего приложения.
DATE может быть сохранен как скоординированное универсальное время (UTC) и преобразован в текущий часовой пояс сеанса следующим образом:
Если вы запустите ALTER SESSION SET TIME_ZONE = '+01:00'; то выход:
и ALTER SESSION SET TIME_ZONE = 'PST'; то выход:
Секундомер
Oracle не обрабатывает прыжки секунд . Дополнительную информацию см. В примечаниях к поддержке 2019397.2 и 730795.1 .
Получение дня недели
Вы можете использовать TO_CHAR( date_value, 'D' ) чтобы получить день недели.
Однако это зависит от параметра сеанса NLS_TERRITORY :
Чтобы сделать это независимо от настроек NLS , вы можете усечь дату до полуночи текущего дня (чтобы удалить любые доли дней) и вычесть дату, усеченную до начала текущей недели iso-week (которая всегда начинается в понедельник):
report this ad
Читайте также: