Сравнение даты Oracle по сравнению с DST

Мы отлаживаем проблему с SQL-запросом, выполняемым с сервера приложений, на котором выполняется Java через Hibernate. Ошибка:

[3/10/14 10:52:07:143 EDT] 0000a984 JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: 1878, SQLState: 22008
[3/10/14 10:52:07:144 EDT] 0000a984 JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions ORA-01878: specified field not found in datetime or interval

Мы смогли сузить это до простого SQL ниже.

select * 
from MY_TABLE T
where T.MY_TIMESTAMP >= (CURRENT_TIMESTAMP - interval '1' hour );

Когда мы запускаем его в той же базе данных, получаем ошибку:

ORA-01878: specified field not found in datetime or interval
01878. 00000 -  "specified field not found in datetime or interval"
*Cause:    The specified field was not found in the datetime or interval.
*Action:   Make sure that the specified field is in the datetime or interval.

Столбец MY_TIMESTAMP определяется как TIMESTAMP(6).

FWIW, если мы изменим сравнение в SQL выше от >= до <=, запрос будет выполнен.

Мы предполагаем, что это имеет какое-то отношение к изменению времени (мы в Америке /New _York), но у нас возникают проблемы с попыткой выяснить, куда идти отсюда с нашей отладкой.

Кроме того, мы видели эту проблему с похожим запросом, который работает через MyBatis, и ошибка выглядит так:

### Error querying database.  Cause: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval

ОБНОВЛЕНИЕ: товарищ по команде в Windows изменил свои настройки даты и времени Windows, сняв флажок "Автоматически настраивать часы для летнего времени", а затем открыл новый экземпляр SQLDeveloper. Второй экземпляр может запускать запрос без каких-либо проблем, но первый (со старой установкой DST) все еще не работает.

Ответ 1

Благодаря kordirko за чрезвычайно подробное описание. Я думаю, что в будущем мы будем рассматривать разные способы сравнения дат, которые не подвержены ошибкам. В то же время мы смогли выяснить проблему и как временное, так и долгосрочное решение.

Во-первых, более подробная информация о проблеме. Оказывается, что значения, хранящиеся в поле TIMESTAMP в базе данных, были неверными. Мы увидели это с помощью функции дампа и изучения байтов. Если вы посмотрите на 5-й байт на выходе ниже, вы увидите час (это на самом деле час + 1, поэтому 5 - это фактически 4AM). Для значений между 3AM и 4AM вы заметите, что 5-й байт равен 3, который представляет 2AM. 2 марта 9 марта 2014 года в EST не существует - это неправильное время согласно правилам DST и правила Oracle.

09-MAR-14 03.06.21.522000000 AM         Typ=180 Len=11: 120,114,3,9,3,7,22,31,29,22,128
09-MAR-14 03.32.37.869000000 AM         Typ=180 Len=11: 120,114,3,9,3,33,38,51,203,227,64
09-MAR-14 03.36.49.804000000 AM         Typ=180 Len=11: 120,114,3,9,3,37,50,47,236,17,0
09-MAR-14 03.43.47.328000000 AM         Typ=180 Len=11: 120,114,3,9,3,44,48,19,140,226,0
09-MAR-14 03.47.55.255000000 AM         Typ=180 Len=11: 120,114,3,9,3,48,56,15,50,253,192
09-MAR-14 03.55.45.129000000 AM         Typ=180 Len=11: 120,114,3,9,3,56,46,7,176,98,64
09-MAR-14 04.05.03.325000000 AM         Typ=180 Len=11: 120,114,3,9,5,6,4,19,95,27,64
09-MAR-14 04.28.41.267000000 AM         Typ=180 Len=11: 120,114,3,9,5,29,42,15,234,24,192
09-MAR-14 04.35.16.072000000 AM         Typ=180 Len=11: 120,114,3,9,5,36,17,4,74,162,0
09-MAR-14 04.41.07.260000000 AM         Typ=180 Len=11: 120,114,3,9,5,42,8,15,127,73,0
09-MAR-14 04.46.31.047000000 AM         Typ=180 Len=11: 120,114,3,9,5,47,32,2,205,41,192
09-MAR-14 04.53.33.471000000 AM         Typ=180 Len=11: 120,114,3,9,5,54,34,28,18,227,192

После долгих исследований и обсуждений мы обнуляли то, что наша версия драйвера JDBC Oracle (11.2.0.2) могла вставлять плохие значения. Oracle на странице 11.2.0.3 ссылается на ошибку, похожую на нашу проблему: "9785135 DST-преобразование неверно с использованием jdbc 11g timestamtz". Мы написали быстрый тестовый класс, который вставляет значения с 9 марта 2014 года с 1:50 до 4:00, используя оба драйвера 11.2.0.2 и 11.2.0.3. Вот фрагмент того, что было вставлено в db:

DRIVER_V         JAVA_DATE_AS_STRING              ORACLE_TIMESTAMP                        DUMP(ORACLE_TIMESTAMP)
11.2.0.2.0/11/2  Sun Mar 09 01:50:00 EST 2014     09-MAR-14 01.50.00.000000000 AM         Typ=180 Len=7: 120,114,3,9,2,51,1
11.2.0.2.0/11/2  Sun Mar 09 03:00:00 EDT 2014     09-MAR-14 03.00.00.000000000 AM         Typ=180 Len=7: 120,114,3,9,3,1,1 --Invalid timestamp
11.2.0.3.0/11/2  Sun Mar 09 01:50:00 EST 2014     09-MAR-14 01.50.00.000000000 AM         Typ=180 Len=7: 120,114,3,9,2,51,1
11.2.0.3.0/11/2  Sun Mar 09 03:00:00 EDT 2014     09-MAR-14 03.00.00.000000000 AM         Typ=180 Len=7: 120,114,3,9,4,1,1 --Correct timestamp

Вы заметите, что 5-й байт временной метки во второй строке в 3:00 утра неверен (3). Это было вставлено с использованием версии 11.2.0.2. То же самое значение, вставленное в версию 11.2.0.3, можно найти на четвертой строке с правильным 5-м байтом (4).

Долгосрочное исправление здесь заключается в обновлении нашего драйвера JDBC. Краткосрочное исправление здесь заключалось в том, чтобы найти строки, которые имеют плохие значения, и запустить для них инструкцию обновления из SQL Plus, чтобы снова установить время (с использованием того же значения, но SQL Plus будет их правильно преобразовывать).

Ответ 2

Чтобы избежать этой ошибки, рассмотрите возможность использования явного выражения выражения в предложении where для типа метки времени (timestamp без часового пояса) следующим образом:

select * 
from MY_TABLE T
where T.MY_TIMESTAMP >= cast(CURRENT_TIMESTAMP - interval '1' hour As timestamp );

В качестве альтернативы вы можете явно задать часовой пояс сеанса, например, '-05: 00' - для стандартного (зимнего) времени в Нью-Йорке, используя ALTER SESSION time_zone = '-05:00' или установив переменную окружения ORA_SDTZ во всех клиентских средах, см. эту ссылку для получения дополнительной информации: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG263

Но это также зависит от того, что действительно хранится в столбце timestamp в таблице, например, что представляет собой временная метка 2014-07-01 15:00:00, является ли это "зимнее время" или "летнее время"?

Функция

CURRENT_TIMESTAMP возвращает значение типа данных TIMESTAMP WITH TIME ZONE
ссылку: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions037.htm

При сравнении временных меток и дат Oracle неявно преобразует данные в более точный тип данных , используя часовой пояс сеанса!
См. Эту ссылку → http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG251

В нашем конкретном случае Oracle отличает столбец timestamp к типу timestamp with time zone.

Oracle определяет часовой пояс сеанса из клиентской среды.
Вы можете определить текущий часовой пояс сеанса, используя этот запрос:

select sessiontimezone from dual;

Например, на моем ПК (Win 7), когда отмечен параметр "Автоматическая настройка часов для летнего времени", этот запрос возвращает (в SQLDeveloper):

SESSIONTIMEZONE                                                           
---------------
Europe/Belgrade

Когда я сниму эту опцию в Windows и перезагрузите SQLDeveloper, она дает:

SESSIONTIMEZONE                                                           
---------------
+01:00

В предыдущем часовом поясе сессии есть часовой пояс с именем региона, для которого Oracle использует правила перехода на летнее время для этого региона в вычислениях даты:

alter session set time_zone = 'Europe/Belgrade';
select cast( timestamp '2014-01-29 01:30:00' as timestamp with time zone ) As x,
       cast( timestamp '2014-05-29 01:30:00' as timestamp with time zone ) As y
from dual;

session SET altered.
X                            Y                          
---------------------------- ----------------------------
2014-01-29 01:30:00 EUROPE/B 2014-05-29 01:30:00 EUROPE/B 
ELGRADE                      ELGRADE

Последний часовой пояс использует фиксированное смещение "+01: 00" (всегда "зимнее время" ), а Oracle не применяет к нему никаких правил DST, он просто добавляет фиксированное смещение.

alter session set time_zone = '+01:00';
select cast( timestamp '2014-01-29 01:30:00' as timestamp with time zone ) As x,
       cast( timestamp '2014-05-29 01:30:00' as timestamp with time zone ) As y
from dual;

session SET altered.
X                            Y                          
---------------------------- ----------------------------
2014-01-29 01:30:00 +01:00   2014-05-29 01:30:00 +01:00

Обратите внимание: ради любопытства, что Y приводит к указанному выше, представляет собой два разных раза!!!
014-05-29 01:30:00 EUROPE/BELGRADE - это не то же самое: 2014-05-29 01:30:00 +01:00

но на самом деле это:
014-05-29 01:30:00 EUROPE/BELGRADE равно: 2014-05-29 01:30:00 +02:00

Вышеизложенное только для того, чтобы вы знали о том, как простая "кратковременная проверка" может повлиять на ваши запросы и где копать по какой-то причине, когда пользователи жалуются "этот запрос работал отлично в январе, но дал неправильные результаты в июле".

И еще по теме ORA-01878 - скажем, моя сессия EUROPE/Warsaw, и моя таблица содержит эту метку времени (без часового пояса)

'TIMESTAMP'2014-03-30 2:30:00'

Обратите внимание, что в моем регионе изменение ДСТ в 2014 году происходит на 30 марта в 2:00 утра.
Это просто означает, что в 30 марта, в 2 часа ночи, я должен проснуться и сдвинуть свои часы вперед с 2:00 до 3:00;)

alter session set time_zone = 'Europe/Warsaw';
select cast( TIMESTAMP'2014-03-30 2:30:00' as timestamp with time zone ) As x
from dual;

SQL Error: ORA-01878: podane pole nie zostało znalezione w dacie-godzinie ani w interwale
01878. 00000 -  "specified field not found in datetime or interval"
*Cause:    The specified field was not found in the datetime or interval.
*Action:   Make sure that the specified field is in the datetime or interval.

Oracle знает, что эта временная метка недействительна в моем регионе в соответствии с правилами DST, потому что нет времени 2:30 30 марта - в 2:00 часы перемещаются на 3: 00, и нет времени 2:30. Поэтому Oracle выдает ошибку ORA-01878.

Однако этот запрос работает отлично:

alter session set time_zone = '+01:00';
select cast( TIMESTAMP'2014-03-30 2:30:00' as timestamp with time zone ) As x
from dual;

session SET altered.
X                          
----------------------------
2014-03-30 02:30:00 +01:00

И это является причиной этой ошибки - ваша таблица содержит отметки времени, такие как 2014-03-09 2:30 или так (для Нью-Йорка, где сдвиги DST происходят 9 марта и 2 ноября), и Oracle не знает, как конвертировать их от метки времени (без TZ) до отметки времени с TZ.

Последний вопрос: почему запрос с >= не работает, но запрос с <= отлично работает?

Они работают/не работают, потому что SQLDeveloper возвращает только первые 50 строк (может быть, 100? Это зависит от настроек). Запрос не читает всю таблицу, он останавливается, когда извлекаются первые 50 (100) строк.
Измените "рабочий" запрос на, например:

select sum( EXTRACT(HOUR from MY_TIMESTAMP) ) from MY_TABLE 
where MY_TIMESTAMP <= (CURRENT_TIMESTAMP - interval '1' hour );

Это заставляет запрос читать все строки в таблице, и появляется ошибка, я уверен на 100%.