SQL 在 Oracle 中减去日期 - 数字还是间隔数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9322935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subtracting Dates in Oracle - Number or Interval Datatype?
提问by BYS2
I have a question about some of the internal workings for the Oracle DATE and INTERVAL datatypes. According to the Oracle 11.2 SQL Reference, when you subtract 2 DATE datatypes, the result will be a NUMBER datatype.
我对 Oracle DATE 和 INTERVAL 数据类型的一些内部工作有疑问。根据Oracle 11.2 SQL Reference，当您减去 2 个 DATE 数据类型时，结果将是一个 NUMBER 数据类型。
On cursory testing, this appears to be true:
在粗略的测试中，这似乎是正确的：
CREATE TABLE test (start_date DATE);
INSERT INTO test (start_date) VALUES (date'2004-08-08');
SELECT (SYSDATE - start_date) from test;
will return a NUMBER datatype.
将返回一个 NUMBER 数据类型。
But now if you do:
但现在如果你这样做：
SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;
you get an INTERVAL datatype. In other words, Oracle can convert the NUMBER from the DATE subtraction into an INTERVAL type.
你得到一个 INTERVAL 数据类型。换句话说，Oracle 可以将来自 DATE 减法的 NUMBER 转换为 INTERVAL 类型。
So now I figured I could try putting in a NUMBER datatype directly in the brackets (instead of doing 'SYSDATE - start_date' which results in a NUMBER anyways):
所以现在我想我可以尝试直接在括号中放入一个 NUMBER 数据类型（而不是做 'SYSDATE - start_date' 这会导致一个 NUMBER 反正）：
SELECT (1242.12423) DAY(5) TO SECOND from test;
But this results in the error:
但这会导致错误：
ORA-30083: syntax error was found in interval value expression
So my question is: what's going on here? It seems like subtracting dates should lead to a NUMBER (as demonstrated in SELECT statement #1), which CANNOT be automatically cast to INTERVAL type (as demonstrated in SELECT statement #3). But Oracle seems to be able to do that somehow if you use the DATE subtraction expression instead of putting in a raw NUMBER (SELECT statement #2).
所以我的问题是：这里发生了什么？似乎减去日期应该导致一个 NUMBER（如 SELECT 语句 #1 中所示），它不能自动转换为 INTERVAL 类型（如 SELECT 语句 #3 中所示）。但是，如果您使用 DATE 减法表达式而不是放入原始 NUMBER（SELECT 语句 #2），Oracle 似乎能够以某种方式做到这一点。
Thanks
谢谢
回答by BYS2
Ok, I don't normally answer my own questions but after a bit of tinkering, I have figured out definitively how Oracle stores the result of a DATE subtraction.
好的，我通常不会回答我自己的问题，但经过一些修补，我已经明确地弄清楚 Oracle 如何存储 DATE 减法的结果。
When you subtract 2 dates, the value is not a NUMBER datatype (as the Oracle 11.2 SQL Reference manualwould have you believe). The internal datatype number of a DATE subtraction is 14, which is a non-documented internal datatype (NUMBER is internal datatype number 2). However, it is actually stored as 2 separate two's complement signed numbers, with the first 4 bytes used to represent the number of days and the last 4 bytes used to represent the number of seconds.
当您减去 2 个日期时，该值不是 NUMBER 数据类型（正如Oracle 11.2 SQL 参考手册让您相信的那样）。DATE 减法的内部数据类型编号是 14，这是一个未记录的内部数据类型（NUMBER 是内部数据类型编号 2）。但是，它实际上存储为 2 个单独的二进制补码有符号数，前 4 个字节用于表示天数，后 4 个字节用于表示秒数。
An example of a DATE subtraction resulting in a positive integer difference:
DATE 减法导致正整数差的示例：
select date '2009-08-07' - date '2008-08-08' from dual;
Results in:
结果是：
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
Recall that the result is represented as a 2 seperate two's complement signed 4 byte numbers. Since there are no decimals in this case (364 days and 0 hours exactly), the last 4 bytes are all 0s and can be ignored. For the first 4 bytes, because my CPU has a little-endian architecture, the bytes are reversed and should be read as 1,108 or 0x16c, which is decimal 364.
回想一下，结果表示为 2 个单独的二进制补码，有符号的 4 字节数字。由于在这种情况下没有小数（正好是 364 天和 0 小时），所以最后 4 个字节都是 0，可以忽略。对于前 4 个字节，因为我的 CPU 是小端架构，所以字节是颠倒的，应该读为 1,108 或 0x16c，也就是十进制的 364。
An example of a DATE subtraction resulting in a negative integer difference:
DATE 减法导致负整数差的示例：
select date '1000-08-07' - date '2008-08-08' from dual;
Results in:
结果是：
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
Again, since I am using a little-endian machine, the bytes are reversed and should be read as 255,250,97,224 which corresponds to 11111111 11111010 01100001 11011111. Now since this is in two's complement signed binary numeral encoding, we know that the number is negative because the leftmost binary digit is a 1. To convert this into a decimal number we would have to reverse the 2's complement (subtract 1 then do the one's complement) resulting in: 00000000 00000101 10011110 00100000 which equals -368160 as suspected.
同样，由于我使用的是小端机器，字节被反转，应该读为 255,250,97,224，对应于 11111111 11111010 01100001 11011111。现在因为这是二进制补码符号二进制数字编码，我们知道这个数字负数，因为最左边的二进制数字是 1。要将其转换为十进制数，我们必须反转 2 的补码（减去 1 然后做一个的补码）导致：00000000 00000101 10011110 00100000 等于 -368160 怀疑。
An example of a DATE subtraction resulting in a decimal difference:
产生小数差的 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
The difference between those 2 dates is 0.25 days or 6 hours.
这两个日期之间的差异是 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
Now this time, since the difference is 0 days and 6 hours, it is expected that the first 4 bytes are 0. For the last 4 bytes, we can reverse them (because CPU is little-endian) and get 84,96 = 01010100 01100000 base 2 = 21600 in decimal. Converting 21600 seconds to hours gives you 6 hours which is the difference which we expected.
现在这一次，由于相差0天6小时，所以预计前4个字节为0。对于后4个字节，我们可以将它们反转（因为CPU是little-endian），得到84,96 = 01010100 01100000 基数 2 = 21600（十进制）。将 21600 秒转换为小时会给您 6 小时，这是我们预期的差异。
Hope this helps anyone who was wondering how a DATE subtraction is actually stored.
希望这可以帮助那些想知道 DATE 减法实际上是如何存储的人。
回答by tawman
You get the syntax error because the date math does not return a NUMBER, but it returns an INTERVAL:
您收到语法错误，因为日期数学不返回 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
You need to convert the number in your example into an INTERVAL first using the NUMTODSINTERVAL Function
您需要先使用NUMTODSINTERVAL 函数将示例中的数字转换为 INTERVAL
For example:
例如：
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>
Based on the reverse cast with the NUMTODSINTERVAL() function, it appears some rounding is lost in translation.
基于 NUMTODSINTERVAL() 函数的反向转换，似乎在翻译中丢失了一些舍入。
回答by Michael Milligan
A few points:
几点：
Subtracting one date from another results in a number; subtracting one timestamp from another results in an interval.
Oracle converts timestamps to dates internally when performing timestamp arithmetic.
Interval constants cannot be used in either date or timestamp arithmetic.
从另一个日期中减去一个日期会得到一个数字；从另一个时间戳中减去一个时间戳会得到一个间隔。
Oracle 在执行时间戳算法时在内部将时间戳转换为日期。
时间间隔常量不能用于日期或时间戳算法。
回答by Sandeep Raul
Use extract() function to retrieve hour / minute / seconds from interval value. See below example, how to get hours from two timestamp columns. Hope this helps!
使用 extract() 函数从间隔值中检索小时/分钟/秒。请参见下面的示例，如何从两个时间戳列中获取小时数。希望这可以帮助！
select IHB_INS_TS, MAIL_SENT_TS, extract( hour from (IHB_INS_TS - MAIL_SENT_TS) ) hourDiff from IHB_ADJSMT_WKFL_NTFCTN;
选择 IHB_INS_TS，MAIL_SENT_TS，从 IHB_ADJSMT_WKFL_NTFCTN 中提取（小时来自（IHB_INS_TS - MAIL_SENT_TS））小时差异；
回答by Pankaj Pathak
select TIMEDIFF (STR_TO_DATE('07:15 PM', '%h:%i %p') , STR_TO_DATE('9:58 AM', '%h:%i %p'))
选择 TIMEDIFF (STR_TO_DATE(' 07:15 PM', '%h:%i %p') , STR_TO_DATE(' 9:58 AM', '%h:%i %p'))
