在 Oracle 中减去日期 - 数字还是间隔数据类型?
我对 Oracle DATE 和 INTERVAL 数据类型的一些内部工作有疑问.根据Oracle 11.2SQL 参考，当你减去 2 个 DATE 数据类型时，结果将是一个 NUMBER 数据类型.
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.
粗略测试，这似乎是正确的:
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;
将返回 NUMBER 数据类型.
will return a NUMBER datatype.
但现在如果你这样做了:
But now if you do:
SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;
你得到一个 INTERVAL 数据类型.换句话说，Oracle 可以将 DATE 减法中的 NUMBER 转换为 INTERVAL 类型.
you get an INTERVAL datatype. In other words, Oracle can convert the NUMBER from the DATE subtraction into an INTERVAL type.
所以现在我想我可以尝试直接在括号中放入一个 NUMBER 数据类型(而不是执行 'SYSDATE - start_date' 无论如何都会产生一个 NUMBER):
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):
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 似乎能够以某种方式做到这一点.
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).
谢谢
推荐答案
好的，我通常不会回答我自己的问题，但经过一些修补后，我已经明确地弄清楚 Oracle 如何存储 DATE 减法的结果.
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.
当您减去 2 个日期时，该值不是 NUMBER 数据类型(如 Oracle 11.2 SQL 参考手册会让您相信).DATE 减法的内部数据类型编号是 14，这是一个未记录的内部数据类型(编号是 internal数据类型编号 2).但是，它实际上存储为 2 个单独的二进制补码有符号数，前 4 个字节用于表示天数，后 4 个字节用于表示秒数.
When you subtract 2 dates, the value is not a NUMBER datatype (as the Oracle 11.2 SQL Reference manual would 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.
产生正整数差的 DATE 减法示例:
An example of a DATE subtraction resulting in a positive integer difference:
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 个单独的二进制补码，有符号的 4 字节数字.由于在这种情况下没有小数(正好是 364 天和 0 小时)，所以最后 4 个字节都是 0，可以忽略.前4个字节，因为我的CPU是little-endian架构，字节颠倒了，应该读为1108或0x16c，也就是十进制的364.
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.
产生负整数差的 DATE 减法示例:
An example of a DATE subtraction resulting in a negative integer difference:
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.
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.
产生小数差的 DATE 减法示例:
An example of a DATE subtraction resulting in a decimal difference:
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 小时.
The difference between those 2 dates is 0.25 days or 6 hours.
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是little-endian)，得到84，96 = 01010100 01100000 基数 2 = 21600(十进制).将 21600 秒转换为小时，您会得到 6 小时，这是我们预期的差异.
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.
希望这能帮助那些想知道 DATE 减法实际上是如何存储的人.
Hope this helps anyone who was wondering how a DATE subtraction is actually stored.
相关文章