Есть ли максимальная разница в датах допустимая в запросе?

Question

Работаю над запросом, который ищет строки в таблице, где дата last_run + frequency (в минутах), больше, чем текущая дата. Заметил, что, похоже, существует верхняя граница для сравнения дат, которую БД может понять.

Например, этот запрос:

with tests as (
    select 'TEST 1'  as code, 99999999 as frequency, sysdate as last_run
    from dual union all
    select 'TEST 2'  as code, 99999999999 as frequency, sysdate as last_run
    from dual)
select p.*, (p.last_run + p.frequency / 24 / 60) as next_run
from tests p
where (p.last_run + p.frequency / 24 / 60 < sysdate or p.last_run is null)

Ожидаю, что этот запрос ничего не вернет, но вместо этого он возвращает:

CODE	FREQUENCY	LAST_RUN	NEXT_RUN
TEST 2	99999999999	05-OCT-2021 10:15:46 AM	15-APR-4455 08:54:46 PM

Кажется странным, что БД не может распознать, что год 4455 > 2021.

Есть ли какая-то максимально возможная разница в датах, которая мне неизвестна?

_{Свободный перевод вопроса Is There a Maximum Date Difference Oracle Can Interpret от участника @Peter Sanders}

Answer 1

Кажется странным, что БД не может распознать, что год 4455 > 2021.

Может распознать. Дело в том, что год не 4455, а -4455. См. db<>fiddle, показывающий результат с форматом по умолчанию DD-MON-RR, с форматом как в вопросе, и форматом ISO с включенным знаком года (первая S в шаблоне формата).

CODE	FREQUENCY	LAST_RUN	NEXT_RUN
TEST 2	99999999999	2021-10-05 17:16:21	-4454-03-12 03:55:21

Значением 99.999.999.999, которое добавляется к текущей дате, составляет 69444444 дня, что приблизительно 190128 лет. Очевидно, что это значительно превышает максимальную дату 9999-12-31. И действительно, при другом значении, например 9.999.999.999 (одну цифру меньше), которое составляет 6944444 дня или приблизительно 19012 лет, будет ошибка см. db<>fiddle.

Проблема, по-видимому, заключается в том, как БД манипулирует своим внутренним представлением даты при выполнении вычислений. При добавлении большого значения, думаю, что год - который хранится в двух байтах, переполняется и начинается отсчёт заново.

Используя тип даты 13, 190128+2021 = 192149, что составляет (256 * 750) + 149. 750 не помещается в один байт, поэтому получается деление с остатком, которое равно 238. Таким образом, первые два байта вычисленной даты будут равны 149,238. Это фактически соответствует году -4459:

select dump(date '-4459-01-01') from dual;

Typ=13 Len=8: 149,238,1,1,0,0,0,0

Достаточно, чтобы убедится, что именно так и происходит учитывая, что вычисления выходят за пределы ожидаемого диапазона и, вероятно, ещё там выполняются недействительные вычисления високосного года. Суть, однако, в том, что это "развернутое" значение представляет собой действительный год во внутренней нотации.

Те же расчёты с меньшим значением 19012+2021 = 20133, что составляет (256 * 82) + 41. Теперь переполнения нет, поэтому первые два байта вычисленной даты получаются как 41,82. Теперь это не действительный год, поэтому и происходит исключение ORA-01841.

Следовательно, необходимо ограничить значение числом, которое никогда не выйдет за пределы 9999-12-31, или проверить его во время выполнения на 9999-12-31 минус текущая дата, если оно слишком большое, проигнорировать его.

_{Свободный перевод ответа от участника @Alex Poole}

БЛОГ НА HUSL

Есть ли максимальная разница в датах допустимая в запросе?

Ответы (1 шт):