在Oracle中减去时间戳,返回奇怪的数据 [英] Subtracting timestamp in oracle returning weird data
问题描述
我正在尝试减去两个日期,并期望返回一些浮动值.但是我得到的回报如下:
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
将值乘以86400(我想以秒为单位)就得到了返回值甚至更奇怪的值:
Multiplying the value by 86400 (I want to get the difference in second) is getting something even more strange value being returned:
+000000007 05:24:00.000000000
有什么主意吗?我怀疑这与类型转换有关.
any idea? I'm suspecting is has something to do with type casting.
推荐答案
我想您的列定义为timestamp
而不是date
.
I guess your columns are defined as timestamp
rather than date
.
减去时间戳记的结果是interval
,而减去date
列的结果是一个数字,代表两个日期之间的天数.
The result of subtracting timestamps is an interval
whereas the result of subtracting date
columns is a number representing the number of days between the two dates.
这在手册中有记录:
http://docs.oracle.com/cd/E11882_01 /server.112/e41084/sql_elements001.htm#i48042
This is documented in the manual:
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;
修改:
如果要转换间隔,例如秒(以数字为单位),您可以执行以下操作:
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;
以上结果为55499.5802
这篇关于在Oracle中减去时间戳,返回奇怪的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!