sql - 从间隔数据类型中提取总秒数

Question

当减去 timestamps返回值是 interval数据类型。是否有一种优雅的方法可以将此值转换为间隔中的(毫/微)秒总数，即整数。

以下方法可行，但不是很漂亮:

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 中是否有更优雅的方法？

Answer 1

我希望这会有所帮助:

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>