Oracle 中的日期相减 - 数字还是区间数据类型？
我对 Oracle DATE 和 INTERVAL 数据类型的一些内部工作有疑问。根据 Oracle 11.2 SQL 参考，当您减去 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 减法表达式而不是放入原始数字（SELECT 语句 #2），Oracle 似乎能够以某种方式做到这一点。
谢谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
好吧，我通常不会回答我自己的问题，但经过一番修改后，我已经明确地弄清楚了 Oracle 如何存储 DATE 减法的结果。
当您减去 2 个日期时，该值不是 NUMBER 数据类型（如 Oracle 11.2 SQL 参考手册会让你相信）。 DATE 减法的内部数据类型编号为 14，这是一个未记录的内部数据类型（编号为 internal数据类型号 2)。然而，它实际上存储为 2 个独立的二进制补码有符号数字，前 4 个字节用于表示天数，后 4 个字节用于表示秒数。
导致正整数差值的 DATE 减法示例：
结果：
回想一下，结果表示为 2 个单独的二进制补码有符号 4 字节数字。由于本例中没有小数（正好是 364 天和 0 小时），因此最后 4 个字节全为 0，可以忽略。对于前 4 个字节，因为我的 CPU 具有小端架构，所以字节被反转，应读取为 1,108 或 0x16c，即十进制 364。
导致负整数差的 DATE 减法示例：
结果为：
同样，由于我使用的是小端机器，因此字节被反转，应读取为 255,250,97,224，对应于11111111 11111010 01100001 11011111。现在，由于这是二进制补码有符号二进制数字编码，我们知道该数字是负数，因为最左边的二进制数字是 1。要将其转换为十进制数字，我们必须反转 2 的补码（减去1 然后进行补码）结果为：00000000 00000101 10011110 00100000 等于怀疑的-368160。
DATE 相减导致小数差异的示例：
这 2 个日期之间的差异为 0.25 天或 6 小时。
现在，由于相差 0 天和 6 小时，因此预计前 4 个字节为 0。对于后 4 个字节，我们可以将它们反转（因为 CPU 是小端）并得到 84,96 = 01010100 01100000 以 2 为基数 = 十进制 21600。将 21600 秒转换为小时可得出 6 小时，这是我们预期的差异。
希望这可以帮助任何想知道 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.
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:
Results in:
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:
Results in:
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:
The difference between those 2 dates is 0.25 days or 6 hours.
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.
您会收到语法错误，因为日期数学不返回数字，但它返回间隔：
您需要首先使用 NUMTODSINTERVAL 函数
例如：
根据使用 NUMTODSINTERVAL() 函数进行的反向转换，似乎在转换过程中丢失了一些舍入。
You get the syntax error because the date math does not return a NUMBER, but it returns an INTERVAL:
You need to convert the number in your example into an INTERVAL first using the NUMTODSINTERVAL Function
For example:
Based on the reverse cast with the NUMTODSINTERVAL() function, it appears some rounding is lost in translation.
几点：
从一个日期中减去另一个日期会得到一个数字；从一个时间戳中减去另一个时间戳会得到一个时间间隔。
Oracle 在执行时间戳算术时会在内部将时间戳转换为日期。
间隔常量不能用于日期或时间戳算术。
Oracle 11gR2 SQL 参考日期时间矩阵
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 11gR2 SQL Reference Datetime Matrix
使用 extract() 函数从间隔值中检索小时/分钟/秒。请参阅下面的示例，了解如何从两个时间戳列获取小时数。希望这有帮助！
选择 INS_TS, MAIL_SENT_TS, extract( 小时 (INS_TS - MAIL_SENT_TS) ) hourDiff from MAIL_NTFCTN;
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!
select INS_TS, MAIL_SENT_TS, extract( hour from (INS_TS - MAIL_SENT_TS) ) hourDiff from MAIL_NTFCTN;
选择 TIMEDIFF (STR_TO_DATE('07:15 PM', '%h:%i %p') 、 STR_TO_DATE('9:58 AM', '%h: %i %p'))
select TIMEDIFF (STR_TO_DATE('07:15 PM', '%h:%i %p') , STR_TO_DATE('9:58 AM', '%h:%i %p'))