减去oracle中的时间戳返回奇怪的数据

我正在尝试减去两个日期并期望返回一些浮点值。但我得到的回报如下：

+000000000 00:00:07.225000

将值乘以 86400（我想得到秒内的差值）会返回更奇怪的值：

+000000007 05:24:00.000000000

任何想法？我怀疑这与类型转换有关。

我猜你的列被定义为timestamp而不是date.

减去时间戳的结果是interval而减去的结果datecolumns 是一个数字，表示两个日期之间的天数。

这在手册中有记录：
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

因此，当您将时间戳列转换为日期时，您应该得到您所期望的结果：

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:

如果你想转换间隔，例如秒数（作为数字），你可以这样做：

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;

上面的结果是55499.5802