在Oracle中计算日期差值，使用数字或时间间隔数据类型？

Question

在Oracle中计算日期差值，使用数字或时间间隔数据类型？

26

我对 Oracle DATE 和 INTERVAL 数据类型的内部工作原理有疑问。根据Oracle 11.2 SQL参考文档，当您从两个 DATE 数据类型中减去时，结果将是一个 NUMBER 数据类型。

经过初步测试，这似乎是正确的：

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

会返回一个数字数据类型。

但是现在如果您执行以下操作：

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

你会得到一个INTERVAL数据类型。换句话说，Oracle可以将从日期相减的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

我的问题是：这里到底发生了什么？减去日期应该会导致一个数字（如SELECT语句#1所示），但这个数字不能自动转换为时间间隔类型（如SELECT语句#3所示）。但是如果您使用日期相减表达式而不是输入原始数字（SELECT语句＃2），Oracle似乎可以以某种方式做到这一点。

谢谢

- BYS2

当你使用类似 to_date('20120216', 'yyyymmdd') 这样的东西代替 sysdate 时，会发生这种情况吗？ - A.B.Cade

抱歉，直到现在才看到你的评论:S..首先回答你的问题，是的，即使使用'to_date()'而不是SYSDATE，它也会这样做。另外，我设法弄清楚了日期减法是如何存储的，所以我将其发布为我的答案:D..由于互联网上似乎没有其他人写过这个，我想写出我发现的东西。 - BYS2

@BYS2 - 你为什么要在括号里放一个数字，然后尝试从数字中减去天数并将其转换为秒：SELECT (1242.12423) DAY(5) TO SECOND from test？这就是你需要问的。 - Art

5个回答

10

您之所以出现语法错误，是因为日期数学不返回数字，而是返回间隔时间：

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函数将示例中的数字转换为时间间隔。

例如：

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() 函数的反向转换，似乎在转换过程中丢失了一些四舍五入的值。

- tawman

啊，多出来的5秒可能是指挂钟时间，因为SYSDATE是参考点。我猜测没有四舍五入。 - tawman

谢谢你的回复，但这很奇怪，因为dump()函数返回的类型是“14”。但是YEAR TO MONTH INTERVAL是类型182，DAY TO SECOND是类型183（http://docs.oracle.com/cd/B10500_01/appdev.920/a96584/oci03typ.htm#421773）...此外，如果日期相减确实返回间隔，那么为什么SQL*Plus显示减去日期时的“2748.9515”？官方参考手册为什么说日期相减的结果是NUMBER？ - BYS2

日期减法显然会产生浮点数。（有没有一些非常古老的Oracle文档表明这可能曾经是这样？）尝试 SELECT * FROM DBA_TYPES WHERE TYPE_OID = '0000000000000000000000000000000E'。还要看看包SYS.STANDARD，那里似乎FLOAT与NUMBER实际上是相同的（即subtype FLOAT is NUMBER）。分享并享受。 - Bob Jarvis - Слава Україні

1

@BobJarvis 从Oracle 11g的DUMP()函数可以清楚地看出，日期相减返回Typ=14。旧文档中没有太多价值。在Google上搜索Oracle日期间隔“Typ=14”，有一些关于Type 14的讨论，但并没有确切的文档记录。 - tawman

我想通了！:D 请阅读我发布的解决方案。虽然感谢您的输入，但非常感激。 - BYS2

0

一些要点：

从一个日期中减去另一个日期会得到一个数字；从一个时间戳中减去另一个时间戳会得到一个间隔。
在执行时间戳算术运算时，Oracle 会将时间戳转换为日期进行内部计算。
无法在日期或时间戳算术运算中使用间隔常量。

Oracle 11gR2 SQL 参考日期矩阵

- Michael Milligan

嘿，感谢您的意见，但请看我的回答。在这种情况下，Oracle文档实际上是不正确/不完整的。 - BYS2

0

使用extract()函数从时间间隔值中检索小时/分钟/秒。请参阅以下示例，了解如何从两个时间戳列中获取小时。希望这可以帮助您！

选择 INS_TS、MAIL_SENT_TS、extract(hour from(INS_TS - MAIL_SENT_TS))hourDiff 从 MAIL_NTFCTN;

- Sandeep Raul

-5

选择 TIMEDIFF (STR_TO_DATE('07:15 PM', '%h:%i %p') , STR_TO_DATE('9:58 AM', '%h:%i %p'))

- Pankaj Pathak

1

这并没有尝试回答问题，并且对于Oracle来说不是有效的语法；您是否错过了标题和问题中的Oracle标签和参考？ - Alex Poole

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BYS2 · Accepted Answer

好的，我通常不回答自己的问题，但经过一些尝试后，我确定了Oracle如何存储DATE减法结果的方法。

当你对两个日期进行减法运算时，结果不是NUMBER数据类型（正如Oracle 11.2 SQL参考手册所说）。DATE减法的内部数据类型是14，这是一个未经记录的内部数据类型（NUMBER是内部数据类型号2）。然而，它实际上被存储为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字节，因为我的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然后执行一次按位取反），结果为：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小时，这正是我们预期的差异。

希望这能帮助那些想知道日期相减实际上是如何存储的人。