sql - 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.