在Oracle-数字或间隔数据类型中减去日期?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (26)

我对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减法中的NUMBER转换为INTERVAL类型。

所以现在我想我可以尝试直接在括号中放入NUMBER数据类型(而不是执行'SYSDATE - start_date',这会导致NUMBER):

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所示)。但是,如果使用DATE减法表达式而不是放入原始NUMBER(SELECT语句#2),Oracle似乎能够以某种方式做到这一点。

提问于
用户回答回答于

好吧,我通常不会回答自己的问题,但经过一番修改之后,我已经明确地确定了Oracle如何存储DATE减法的结果。

当您减去2个日期时,该值不是NUMBER数据类型。DATE减法的内部数据类型编号为14,这是一个未记录的内部数据类型。然而,它实际上是作为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

回想一下,结果表示为2个独立的2的补码,有符号的4个字节的数字。由于在这种情况下没有小数(完全是364天和0小时),所以最后4个字节都是0,可以忽略。对于前4个字节,由于我的CPU具有小端架构,所以字节反转,应该读为1,108或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

同样,因为我使用的是小端机,所以字节反转,应该读作255,250,97,224,它对应于11111111 11111010 01100001 11011111。现在,由于这是用二进制补码签署的二进制数字编码,所以我们知道数字是否则是因为最左边的二进制数字是1.要将其转换为十进制数,我们必须反转2的补码(减1,然后补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个字节,我们可以将它们反转(因为CPU是小尾数)并得到84,96 = 01010100 01100000基数2 = 21600十进制。将21600秒转换为小时可以让您获得6小时,这与我们预期的不同。

希望这有助于任何想知道如何实际存储DATE减法的人。

用户回答回答于

你会得到语法错误,因为日期数学不返回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

你需要首先使用NUMTODSINTERVAL函数将示例中的数字转换为INTERVAL

例如:

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>

扫码关注云+社区