Extracting the total number of seconds from an interval data-type

Question

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 )

Is there a more elegant method in SQL or PL/SQL?

Answer 1

An easy way:

select extract(day from (ts1-ts2)*86400) from dual;

The idea is to convert the interval value into days by times 86400 (= 24*60*60). Then extract the 'day' value which is actually second value we wanted.

Answer 2

I hope this help:

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

With your test:

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> 

Answer 3

I've found this to work. Apparently, if you do arithmetics with timestamps they are converted to some internal datatype that, when substracted from each other, returns the interval as a number.

Easy? Yes. Elegant? No. Gets the work done? Oh yeah.

SELECT ( (A + 0) - (B + 0) ) * 24 * 60 * 60 
FROM
(
   SELECT SYSTIMESTAMP A,
          SYSTIMESTAMP - INTERVAL '1' MINUTE B
   FROM DUAL
);

Answer 4

Unfortunately, I don't think that there is an alternative (or more elegant) way of calculating total seconds from an interval type in pl/sql. As this article mentions:

... unlike .NET, Oracle provides no simple equivalent to TimeSpan.TotalSeconds.

therefore extracting day, hour etc from the interval and multiplying them with corresponding values seems like the only way.