Сравнение даты в Oracle нарушено из-за летнего времени
Мы отлаживали проблему с помощью 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) все еще не работает.
2 ответа
Спасибо Кордирко за чрезвычайно подробное описание. Я думаю, что в будущем мы будем искать разные способы сравнения дат, которые не так подвержены ошибкам. В то же время мы смогли выяснить проблему, как временное, так и долгосрочное решение.
Во-первых, подробнее о проблеме. Оказывается, значения, хранящиеся в поле TIMESTAMP в базе данных, были неверными. Мы увидели это с помощью функции dump и изучения байтов. Если вы посмотрите на 5-й байт в выходных данных ниже, вы увидите час (на самом деле это час + 1, так что 5 на самом деле 4 утра). Для значений от 3 до 4 часов вы заметите, что 5-й байт равен 3, что соответствует 2 часам. 2:00 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
После долгих исследований и обсуждений мы сосредоточились на том факте, что наша версия драйвера Oracle JDBC (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. Вот фрагмент того, что было вставлено в БД:
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 AM неверен (3). Это было вставлено с использованием версии 11.2.0.2. То же значение, введенное в версии 11.2.0.3, можно найти в четвертой строке с правильным 5-м байтом (4).
Долгосрочное исправление здесь - обновить наш драйвер JDBC. Краткосрочное исправление здесь заключалось в том, чтобы найти строки с неправильными значениями и запустить оператор обновления для них из SQL Plus, чтобы снова установить время (используя то же значение, но SQL Plus преобразует их правильно).
Чтобы избежать этой ошибки, рассмотрите возможность использования явного приведения выражения в предложении where к типу отметки времени (отметка времени без часового пояса) следующим образом:
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
Но это также зависит от того, что действительно хранится в столбце метки времени в таблице, например, что такое метка времени 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
В нашем конкретном случае 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:00 ночи, я должен проснуться и перевести свои часы вперед с 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 знает, что эта временная метка недопустима в моем регионе в соответствии с правилами летнего времени, потому что 30 марта нет времени 2: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 или около того (для Нью-Йорка, где смена летнего времени происходит 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%.