从间隔数据类型中提取总秒数 [英] Extracting the total number of seconds from an interval data-type
本文介绍了从间隔数据类型中提取总秒数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当减去timestamps
时,返回值是interval
数据类型.是否有一种优雅的方法可以将此值转换为间隔中的(毫秒/微秒)秒总数,即整数.
When subtracting timestamps
the return value is an interval
data-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer.
下面的方法可以工作,但是不是很漂亮:
The following would work, but it's not very pretty:
select abs( extract( second from interval_difference )
+ extract( minute from interval_difference ) * 60
+ extract( hour from interval_difference ) * 60 * 60
+ extract( day from interval_difference ) * 60 * 60 * 24
)
from ( select systimestamp - (systimestamp - 1) as interval_difference
from dual )
在SQL或PL/SQL中是否有更优雅的方法?
Is there a more elegant method in SQL or PL/SQL?
推荐答案
我希望此帮助:
zep@dev> select interval_difference
2 ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
3 from (select systimestamp - (systimestamp - 1) as interval_difference
4 from dual)
5 ;
INTERVAL_DIFFERENCE FRACT_SEC_DIFFERENCE
------------------------------------------------------------------------------- --------------------
+000000001 00:00:00.375000 86400,375
通过测试:
zep@dev> select interval_difference
2 ,abs(extract(second from interval_difference) +
3 extract(minute from interval_difference) * 60 +
4 extract(hour from interval_difference) * 60 * 60 +
5 extract(day from interval_difference) * 60 * 60 * 24) as your_sec_difference
6 ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
7 ,round(sysdate + (interval_difference * 86400) - sysdate) as sec_difference
8 ,round((sysdate + (interval_difference * 86400) - sysdate) * 1000) as millisec_difference
9 from (select systimestamp - (systimestamp - 1) as interval_difference
10 from dual)
11 /
INTERVAL_DIFFERENCE YOUR_SEC_DIFFERENCE FRACT_SEC_DIFFERENCE SEC_DIFFERENCE MILLISEC_DIFFERENCE
------------------------------------------------------------------------------- ------------------- -------------------- -------------- -------------------
+000000001 00:00:00.515000 86400,515 86400,515 86401 86400515
zep@dev>
这篇关于从间隔数据类型中提取总秒数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文