oracle 在oracle中减去时间戳返回奇怪的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10348140/
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 timestamp in oracle returning weird data
提问by You Qi
I'm trying to subtract two dates and expecting some floating values return. But what I got in return is as below:
我正在尝试减去两个日期并期望返回一些浮动值。但我得到的回报如下：
+000000000 00:00:07.225000
Multiplying the value by 86400 (I want to get the difference in second) is getting something even more strange value being returned:
将值乘以 86400（我想得到秒的差异）会得到一些更奇怪的值被返回：
+000000007 05:24:00.000000000
any idea? I'm suspecting is has something to do with type casting.
任何的想法？我怀疑这与类型转换有关。
回答by a_horse_with_no_name
I guess your columns are defined as timestamprather than date.
我猜你的列被定义为timestamp而不是date.
The result of subtracting timestamps is an intervalwhereas the result of subtracting datecolumns is a number representing the number of days between the two dates.
减去时间戳interval的结果是一个，而减去date列的结果是一个数字，表示两个日期之间的天数。
This is documented in the manual:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i48042
这在手册中有记录：http:
//docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i48042
So when you cast your timestamp columns to date, you should get what you expect:
因此，当您将时间戳列转换为日期时，您应该得到您期望的结果：
with dates as (
select timestamp '2012-04-27 09:00:00' as col1,
timestamp '2012-04-26 17:35:00' as col2
from dual
)
select col1 - col2 as ts_difference,
cast(col1 as date) - cast(col2 as date) as dt_difference
from dates;
Edit:
编辑：
If you want to convert the interval so e.g. the number of seconds (as a number), you can do something like this:
如果您想转换间隔，例如秒数（作为数字），您可以执行以下操作：
with dates as (
select timestamp '2012-04-27 09:00:00.1234' as col1,
timestamp '2012-04-26 17:35:00.5432' as col2
from dual
)
select col1 - col2 as ts_difference,
extract(hour from (col1 - col2)) * 3600 +
extract(minute from (col1 - col2)) * 60 +
(extract(second from (col1 - col2)) * 1000) / 1000 as seconds
from dates;
The result of the above is 55499.5802
上面的结果是 55499.5802
回答by buzznfrog
Read this article: http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
阅读这篇文章：http: //asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
Example:
例子：
create table yourtable(
date1 date,
date2 date
)
SQL> select datediff( 'ss', date1, date2 ) seconds from yourtable
SECONDS
----------
6269539
