Как неявно конвертируется тип данных DATE в NUMBER?

Question

Пытаюсь понять это выражение:

NVL2(NULL, (SYSDATE - SYSDATE), DATE'2020-07-29'))

Оно вернёт значение: 2459060

Никак не пойму, откуда берётся это число? Так как, SYSDATE-SYSDATE это число, но дату же нельзя так просто преобразовать в число:

TO_NUMBER(DATE'2020-07-29')
ORA-01722: invalid number

В документации SQL Language Reference NVL2 сказано, что если выражение второго аргумента число, то последующий аргумент будет неявно преобразован к числу:

If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

Поэтому вопрос, какая конвертация используется неявно, чтобы тип данных DATEпреобразовать в NUMBER?

_{Свободный перевод вопроса How does Oracle SQL convert DATE to NUMBER implicitly? от участника @Nicholas Koldys}

Answer 1

По факту выполняется это:

select to_number (to_char (date'2020-07-29','J')) days from dual;

      DAYS
----------
   2459060

Где J, это число дней прошедших с 1 января 4712 до н.э., то есть то значение, которое возвращает выражение в вопросе.

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

select nvl2 (null, 99, date'2020-05-29') from dual;
Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Если сдедлать дамп результата вычитания дат, то он возвращает не число (Typ=2), а другой, внутренний и недокументированный, насколько я вижу, тип данных:

select dump (sysdate-sysdate) dump from dual;

DUMP
--------------------------------
Typ=14 Len=8: 0,0,0,0,0,0,0,0

Очевидно, что это приводит к тому, что третий аргумент приводится к тому же самому типу, к которому он наиболее эквивалентен:

select date'2020-07-29'-date'-4712-01-01' days from dual;

      DAYS
----------
   2459059

Поэтому, выглядит так, что либо делается что-то похожее на последнее, но адаптироанное под этот тип данных, либо делается внутреннее преобразование к формату J, либо что-то ещё другое. Повидимому, это поведение не документированно.

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

Answer 2

Тип результата выражения (date2-date1) не является числом. К численному типу NUMBER он будет приведён при присвоении значения переменной или колонке таблицы. До того, это внутренняя (недокументированная) си структура данных с типом (Typ=14):

var rc refcursor
declare
    dd number := sysdate-(sysdate-125.125);
begin 
    open :rc for select 
        dump (sysdate-(sysdate-125.125), 16) dump, dump (dd, 16) dump, dd ddiff 
        from dual; 
end; 
/

DUMP                             DUMP                                  DDIFF
-------------------------------- -------------------------------- ----------
Typ=14 Len=8: 7d,0,0,0,30,2a,0,0 Typ=2 Len=5: c2,2,1a,d,33           125.125

Где, длина 8 байт, 4 байта - кол-во дней, 3 байта кол-во секунд, последний байт резервирован. На архитектуре с little endian можно посчитать справa налево:

select to_number ('7d', 'xx') days, to_number ('2a30', 'xxxx')/(60*60) hours 
from dual
/

      DAYS      HOURS
---------- ----------
       125          3

То есть, по сути это интервал времени. Выглядит логично, привести дату к интервалу времени используя Юлианскую дату (Julian Days) как вычитаемое:

select (date'2020-07-29')-date'-4712-01-01'+1 days from dual;

      DAYS
----------
   2459060

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

select (sysdate-(sysdate-125.125)) day (3) to second interval from dual;

INTERVAL
--------------------
+125 03:00:00.000000

select (125.125) day (3) to second interval from dual;
Error report -
SQL Error: ORA-30083: syntax error was found in interval value expression

БЛОГ НА HUSL

Как неявно конвертируется тип данных DATE в NUMBER?

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