Вычитание дат в Oracle - количество или интервал Datatype?

У меня есть вопрос о некоторых внутренних функциях для типов данных Oracle DATE и INTERVAL. В соответствии с Oracle 11.2 SQL Reference, когда вы вычитаете 2 типа данных DATE, результатом будет NUMBER тип данных.

При беглом тестировании это выглядит так:

CREATE TABLE test (start_date DATE);
INSERT INTO test (start_date) VALUES (date'2004-08-08');
SELECT (SYSDATE - start_date) from test;

вернет тип данных NUMBER.

Но теперь, если вы это сделаете:

SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;

вы получаете тип данных INTERVAL. Другими словами, Oracle может преобразовать NUMBER из вычитания DATE в тип INTERVAL.

Итак, теперь я решил, что могу попробовать ввести тип данных NUMBER непосредственно в скобках (вместо того, чтобы делать "SYSDATE - start_date", что приводит к НОМЕР в любом случае):

SELECT (1242.12423) DAY(5) TO SECOND from test;

Но это приводит к ошибке:

ORA-30083: syntax error was found in interval value expression

Итак, мой вопрос: что здесь происходит? Кажется, что вычитание дат должно привести к NUMBER (как показано в инструкции SELECT # 1), которая НЕ МОЖЕТ быть автоматически перенесена в тип INTERVAL (как показано в инструкции SELECT № 3). Но Oracle, похоже, может это сделать как-то, если вы используете выражение вычитания DATE вместо того, чтобы вводить необработанный NUMBER (оператор SELECT # 2).

Спасибо

Ответы

Ответ 1

Хорошо, я обычно не отвечаю на собственные вопросы, но после немного возиться, я окончательно выяснил, как Oracle хранит результат вычитания DATE.

Когда вы вычитаете 2 даты, значение не является типом данных NUMBER (в качестве Oracle 11.2 SQL Reference) вы бы поверили). Внутренний тип данных для вычитания DATE равен 14, который является не документированным внутренним типом данных (NUMBER - внутренний тип данных номер 2). Тем не менее, он фактически хранится в виде двух отдельных двух дополняемых номеров, причем первые 4 байта используются для представления количества дней и последних 4 байтов, используемых для представления количества секунд.

Пример вычитания DATE, приводящий к положительной целочисленной разности:

select date '2009-08-07' - date '2008-08-08' from dual;

Результаты в:

DATE'2009-08-07'-DATE'2008-08-08'
---------------------------------
                              364

select dump(date '2009-08-07' - date '2008-08-08') from dual;

DUMP(DATE'2009-08-07'-DATE'2008
-------------------------------
Typ=14 Len=8: 108,1,0,0,0,0,0,0

Напомним, что результат представлен в виде двух отдельных двух дополняемых 4 байтовых номеров. Поскольку в этом случае нет десятичных знаков (ровно 364 дня и 0 часов), последние 4 байта - все 0 и могут быть проигнорированы. Для первых 4 байтов, поскольку мой процессор имеет малоконтинентальную архитектуру, байты обращаются вспять и должны считываться как 1108 или 0x16c, что является десятичным числом 364.

Пример вычитания DATE, приводящий к отрицательной целочисленной разности:

select date '1000-08-07' - date '2008-08-08' from dual;

Результаты в:

DATE'1000-08-07'-DATE'2008-08-08'
---------------------------------
                          -368160

select dump(date '1000-08-07' - date '2008-08-08') from dual;

DUMP(DATE'1000-08-07'-DATE'2008-08-0
------------------------------------
Typ=14 Len=8: 224,97,250,255,0,0,0,0

Опять же, поскольку я использую машину little-endian, байты обращаются вспять и должны быть считаны как 255 250,97,224, что соответствует 11111111 11111010 01100001 11011111. Теперь, поскольку это двоичная кодировка с двумя дополнениями, мы знаем, что число отрицательно, потому что самая левая двоичная цифра равна 1. Чтобы преобразовать это в десятичное число, нам пришлось бы отменить 2 дополнения (вычесть 1, затем сделать одно дополнение), в результате чего: 00000000 00000101 10011110 00100000, что равно -368160, как подозревалось.

Пример вычитания DATE, приводящий к десятичной разности:

select to_date('08/AUG/2004 14:00:00', 'DD/MON/YYYY HH24:MI:SS'
 - to_date('08/AUG/2004 8:00:00', 'DD/MON/YYYY HH24:MI:SS') from dual;

TO_DATE('08/AUG/200414:00:00','DD/MON/YYYYHH24:MI:SS')-TO_DATE('08/AUG/20048:00:
--------------------------------------------------------------------------------
                                                                             .25

Разница между этими двумя датами составляет 0,25 дня или 6 часов.

select dump(to_date('08/AUG/2004 14:00:00', 'DD/MON/YYYY HH24:MI:SS')
 - to_date('08/AUG/2004 8:00:00', 'DD/MON/YYYY HH24:MI:SS')) from dual;

DUMP(TO_DATE('08/AUG/200414:00:
-------------------------------
Typ=14 Len=8: 0,0,0,0,96,84,0,0

Теперь на этот раз, так как разница составляет 0 дней и 6 часов, ожидается, что первые 4 байта равны 0. Для последних 4 байтов мы можем их отменить (потому что процессор малоподобный) и получить 84, 96 = 01010100 01100000 base 2 = 21600 в десятичной системе. Преобразование 21600 секунд в часы дает вам 6 часов, это разница, которую мы ожидали.

Надеюсь, это поможет любому, кто задавался вопросом, как фактически вычитается DATE-вычитание.

Ответ 2

Вы получаете синтаксическую ошибку, потому что математика даты не возвращает NUMBER, но возвращает INTERVAL:

SQL> SELECT DUMP(SYSDATE - start_date) from test;

DUMP(SYSDATE-START_DATE)
-------------------------------------- 
Typ=14 Len=8: 188,10,0,0,223,65,1,0

Вам нужно сначала преобразовать число в свой пример в INTERVAL, используя функцию NUMTODSINTERVAL

Например:

SQL> SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;

(SYSDATE-START_DATE)DAY(5)TOSECOND
----------------------------------
+02748 22:50:04.000000

SQL> SELECT (SYSDATE - start_date) from test;

(SYSDATE-START_DATE)
--------------------
           2748.9515

SQL> select NUMTODSINTERVAL(2748.9515, 'day') from dual;

NUMTODSINTERVAL(2748.9515,'DAY')
--------------------------------
+000002748 22:50:09.600000000

SQL>

Основываясь на обратном преобразовании с помощью функции NUMTODSINTERVAL(), появляется некоторое округление в переводе.

Ответ 3

Несколько точек:

  • Вычитание одной даты из другой приводит к числу; вычитание одной временной метки из другой приводит к интервалу.

  • Oracle выполняет преобразование временных меток в даты, когда выполняется арифметика timestamp.

  • Интервальные константы не могут использоваться в арифметике даты или времени.

Oracle 11gR2 SQL Reference Datetime Matrix

Ответ 4

Используйте функцию extract(), чтобы извлечь часы/минуты/секунды из значения интервала. Ниже приведен пример, как получить часы из двух столбцов отметок времени. Надеюсь это поможет!

выберите IHB_INS_TS, MAIL_SENT_TS, извлекать (час из (IHB_INS_TS - MAIL_SENT_TS)) hourDiff из IHB_ADJSMT_WKFL_NTFCTN;

Ответ 5

выберите TIMEDIFF (STR_TO_DATE (' 07:15 PM', '% h:% i% p'), STR_TO_DATE (' 9:58 AM', % h:% i% p '))