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

sql oracle plsql
Ben picture Ben · Apr 10, 2012 · Viewed 60.8k times · Source

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

Zhaoping Lu picture Zhaoping Lu · Aug 12, 2016

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.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More