Главная

В поисках несуществующего времени

2015-04-07 в 9:37, admin, рубрики: dst, oracle, timezone

В пятницу в приложении установленном на тестовую площадку был найден баг связанный с конфликтом библиотек, который по какой-то причине не проявился на стадии разработки и который стопорил обрабатываемый процесс. Мы оперативно подготовили исправление и передали обновленный дистрибутив команде внедрения. В свою очередь команда внедрения создала запрос команде администрирования на установку дистрибутива на тестовую площадку. В выходной день дежурная смена добралась до этой заявки и обновила приложение. Утром в понедельник обнаружилось что процесс снова застопорился.
Провели анализ логов сервера приложений было обнаружено множество строчек вида

ORA-01878: specified field not found in datetime or interval

Гугл по коду ошибки подсказал мне http://stackoverflow.com/questions/22305466/oracle-date-compare-broken-because-of-dst

Запрос виновник был найден очень быстро — в приложении на spring integration был реализован обработчик заданий примерно следующего вида:

inbound-channel-adapter

<int-jdbc:inbound-channel-adapter query=" SELECT ID, UPDATE_TIME FROM TASK WHERE UPDATE_TIME IS NULL OR SYSTIMESTAMP>(UPDATE_TIME+ INTERVAL '3500' SECOND) SKIP LOCKED"
    channel="target" data-source="dataSource"
    update="update task set UPDATE_TIME = SYSTIMESTAMP where id in (:id)" />
    <int:poller fixed-rate="1000">
    </int:poller>
</int-jdbc:inbound-channel-adapter>

Собственно, виновник

SELECT ID, UPDATE_TIME
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR SYSTIMESTAMP>(UPDATE_TIME+ INTERVAL '3500' SECOND)

Запрос успешно отрабатывал на базе разработчиков, но падал на тестовой базе, Был начат поиск решений.
Сперва по совету из статьи был испробован вариант №1

SELECT ID, UPDATE_TIME 
FROM  TASK 
WHERE 
UPDATE_TIME IS NULL OR SYSTIMESTAMP>CAST(UPDATE_TIME+ INTERVAL '3500' SECOND AS TIMESTAMP WITH TIME ZONE)

Запрос успешно отрабатывал на базе разработчиков, и на тестовой базе из консоли администратора. Был подготовлен и оперативно установлен дистрибутив с исправлением. Который по факту ничего не исправил. Стало понятно, что проблема зависит от параметров подключения сессии.
Были запрошены и получены данные таблиц с тестовой площадки. И две строки сразу вызвали подозрение UPDATE_TIME в них приходился на 29 марта 1:30 ночи – последнее воскресенье марта. После вычисления

UPDATE_TIME+ INTERVAL '3500' SECOND

как раз попадает в интервал между 2:00 и 3:00 ночи -несуществующего времени для временного пояса использующего DST.
Для проверки подозрений в базу разработка были внесены похожие данные – Запрос продолжил работать без сбоев.
Пробую с

alter session set time_zone =’europe/warsaw’

И попадаю в цель – Ошибку удалось повторить на площадке разработки. На этом можно было остановится запросив установку соответствующих обновлений временных зон на базу данных (подробна информация по обновлениям Oracle в конце статьи). Но мне стало интересно можно ли исправить это поведение переписав SQL запрос.

Пробую перенести энтропию из одной части выражения в другую вариант №2

SELECT ID, UPDATE_TIME from TABLE1 WHERE UPDATE_TIME IS NULL OR ( SYSTIMESTAMP - INTERVAL '3500' SECOND )>  UPDATE_TIME

Все Ок, но делаем предположение что SYSTIMESTAMP все равно может принят значение из “несуществующего времени” и соответственно в году возможен один час когда приложение не работает.

Приходим к варианту №3

SELECT ID, UPDATE_TIME 
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR ( SYSTIMESTAMP -  UPDATE_TIME ) > INTERVAL '3500' SECOND

Вроде все хорошо, но что если вставить в таблицу запись с временем между 2:00 и 3:00 ночи. Пробую 29 марта 2:30 ночи – запросы перестают работать.

ORA-01878: specified field not found in datetime or interval

Против лома нет приема — вариант №4

SELECT ID,  UPDATE_TIME 
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR (to_timestamp_tz(to_char(SYSTIMESTAMP,'rrrr-mm-dd hh24:mi:ss')||' '||'UTC','rrrr-mm-dd hh24:mi:ss tzr') -  to_timestamp_tz(to_char(UPDATE_TIME,'rrrr-mm-dd hh24:mi:ss')||' '||'UTC','rrrr-mm-dd hh24:mi:ss tzr') ) > INTERVAL '3500' SECOND

Все работает – но хочется найти решение по проще. Перечитываю статью на stackoverflow и документацию Oracle до наступления просветления:

Проблема заключается в том что UPDATE_TIME в отличии от SYSTIMESTAMP объявлено без временной зоны что приводит к неявному приведению типов в исходном запросе и запросах №2 и 3. Запрос для проверки
```
SELECT ID, CAST(UPDATE_TIME+ INTERVAL '3500' SECOND AS TIMESTAMP WITH TIME ZONE) FROM  TASK 
```
Если воспользоваться LOCALTIMESTAMP вместо SYSTIMESTAMP то все будет работать
```
SELECT ID, UPDATE_TIME
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR LOCALTIMESTAMP >(UPDATE_TIME+ INTERVAL '3500' SECOND)
```
Так же можно сменить тип поля UPDATE_TIME на TIMESTAMP with time zone и не забыть поставить обновления временных зон на Oracle
Можно вынести текущую дату как параметр и передавать из приложения — все будет работать.
Если по каким-то причинам нужен timestamp без временной зоны в сочетании с SYSTIMESTAMP – то приводить надо не к типу возвращаемому SYSTIMESTAMP а к типу поля UPDATE_TIME
```
SELECT ID, UPDATE_TIME
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR CAST(SYSTIMESTAMP AS TIMESTAMP) >(UPDATE_TIME+ INTERVAL '3500' SECOND)
```

З.Ы. Как обещал выше — Информацию описанием установки обновлений часовых поясов базы можно причитать в статье Переход на зимнее время Oracle баз данных в 2014 году

Автор: WaZZuP

Источник