在Oracle中减去日期 - 数字或间隔数据类型？ [英] Subtracting Dates in Oracle - Number or Interval Datatype?

问题描述

我有一些关于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来自测试; 
  

你得到一个INTERVAL数据类型。换句话说，Oracle可以将NUMBER从DATE减法转换为INTERVAL类型。

所以现在我想我可以尝试直接在括号中放入NUMBER数据类型而不是做'SYSDATE - start_date'，这样会导致一个NUMBER）：

  SELECT（1242.12423）DAY（5）TO SECOND从测试; 
  

但这会导致错误：

  ORA-30083：间隔值表达式中发现语法错误
  

谢谢

解决方案

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

当您减去2个日期时，该值不是NUMBER数据类型（如 Oracle 11.2 SQL参考手册将让您相信）。 DATE减法的内部数据类型号为14，这是一个未记录的内部数据类型（NUMBER为 internal数据类型号2 ）。但是，它实际上存储为2个独立的二进制补码，前4个字节用于表示天数，最后4个字节用于表示秒数。

DATE减法的一个例子，产生一个正整数差：

  select date'2009-08-07' -  2008-08-08'from dual; 
  

结果：

  DATE'2009-08-07'-DATE'2008-08-08'
 ----------------------- ---------- 
 364 
 
选择转储（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具有一个小端点架构，字节是相反的，应该被读取为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 
 
选择转储（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

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'） 
 
 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'））从双; 
 
 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个rs这是我们预期的差异。

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

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;

will return a NUMBER datatype.

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.

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;

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).

Thanks

解决方案

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.

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.

An example of a DATE subtraction resulting in a positive integer difference:

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.

An example of a DATE subtraction resulting in a negative integer difference:

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.

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

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

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.

Hope this helps anyone who was wondering how a DATE subtraction is actually stored.

这篇关于在Oracle中减去日期 - 数字或间隔数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！