Oracle - Number还是Interval数据类型中的减去日期？
我有一个关于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可以将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所以我的问题是:这里发生了什么？减去日期似乎应该得到一个数字(如SELECT语句#1所示)，该数字不能自动转换为INTERVAL类型(如SELECT语句#3所示)。但是，如果您使用日期减法表达式而不是输入一个原始数字(SELECT语句#2)，Oracle似乎能够以某种方式做到这一点。
谢谢
回答 3
Stack Overflow用户
发布于 2012-02-19 11:08:18
好吧，我通常不会回答我自己的问题，但经过一些修修补补，我终于弄清楚了Oracle是如何存储日期减法的结果的。
减去2个日期后，该值不是NUMBER数据类型(正如Oracle 11.2 SQL Reference manual希望您相信的那样)。日期减法的内部数据类型number是14，这是一个未记录的内部数据类型(NUMBER为internal datatype number 2)。然而，它实际上存储为2个单独的2补码有符号数字，前4个字节用于表示天数，后4个字节用于表示秒数。
以下是导致正整数差的日期减法示例：
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回想一下，结果被表示为2个独立的2的补码，带符号的4字节数字。因为在这种情况下没有小数(364天和0小时)，最后4个字节全是0，可以忽略。对于前4个字节，因为我的CPU采用小端体系结构，所以字节颠倒过来，应该读取为1,108或0x16c，即十进制364。
以下是导致负整数差的日期减法示例：
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同样，由于我使用的是小端机器，字节被颠倒过来，应该读取为255,250,97,224，相当于11111111 11111010 01100001 11011111。现在由于这是2的补码有符号二进制数字编码，我们知道这个数字是负数，因为最左边的二进制数字是1。要将它转换成十进制数，我们必须反转2的补码(减去1，然后进行1的补码)，结果是: 00000000 00000101 10011110 00100000，这等于-368160。
以下是导致小数差的日期减法示例：
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个字节，我们可以反转它们(因为CPU是小端的)，得到以十进制表示的84,96 = 01010100 01100000基数2= 21600。将21600秒转换为小时会得到6小时，这是我们预期的差值。
希望这对那些想知道日期减法是如何存储的人有所帮助。
Stack Overflow用户
发布于 2012-02-17 12:58:01
您会得到语法错误，因为日期数学不返回数字，但返回一个间隔：
SQL> SELECT DUMP(SYSDATE - start_date) from test;
DUMP(SYSDATE-START_DATE)
--------------------------------------
Typ=14 Len=8: 188,10,0,0,223,65,1,0您需要首先使用NUMTODSINTERVAL Function将示例中的数字转换为时间间隔
例如：
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()函数的反向转换，似乎在转换过程中丢失了一些舍入。
Stack Overflow用户
发布于 2019-06-13 01:20:56
使用extract()函数从interval值中检索小时/分钟/秒。参见下面的示例，如何从两个时间戳列中获取小时数。希望这能有所帮助！
select INS_TS，MAIL_SENT_TS，extract( hour from (INS_TS - MAIL_SENT_TS) ) hourDiff from MAIL_NTFCTN；
https://stackoverflow.com/questions/9322935
复制相似问题