我有一个关于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似乎能够以某种方式做到这一点。
谢谢
发布于 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小时,这是我们预期的差值。
希望这对那些想知道日期减法是如何存储的人有所帮助。
发布于 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()函数的反向转换,似乎在转换过程中丢失了一些舍入。
发布于 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
复制相似问题