SQL Extract Values from Timestamp Difference

I’m trying to extract Days, Hours, Minutes, Seconds from the output of subtracting two timestamps in an Oracle database. I then want to take the extracted values and put them into separate columns. I am able to accomplish this using substr but that doesn’t seem efficient. Is there a more efficient and programmatic way of extracting the values? Below is an example query with the current and desired output.

Example:

SELECT 

to_timestamp('2019-11-10 15:00:00', 'YYYY-MM-DD hh24:mi:ss') -
to_timestamp('2019-10-25 13:25:00', 'YYYY-MM-DD hh24:mi:ss')  
as TIME_DIFF,

SUBSTR(to_timestamp('2019-11-10 15:00:00', 'YYYY-MM-DD hh24:mi:ss') -
to_timestamp('2019-10-25 13:25:00', 'YYYY-MM-DD hh24:mi:ss'), 9, 2)  
as DAYS

from dual

Current Output:

TIME_DIFF                     | DAYS
------------------------------+-----
+000000016 01:35:00.000000000 | 16

Desired Output:

DAYS | HOUR | MIN | SS
-----+------+-----+---+
16   |  01  |  35 | 00

Answer

You can use extract() to pull out the required values from the interval:

with t as (
    select to_timestamp('2019-11-10 15:00:00', 'YYYY-MM-DD hh24:mi:ss') -
        to_timestamp('2019-10-25 13:25:00', 'YYYY-MM-DD hh24:mi:ss')  
        as TIME_DIFF
    from dual
)
select 
    extract(day from time_diff) days,
    extract(hour from time_diff) hours,
    extract(minute from time_diff) minutes,
    extract(second from time_diff) seconds
from t

Demo on DB Fiddle:

DAYS | HOURS | MINUTES | SECONDS
---: | ----: | ------: | ------:
  16 |     1 |      35 |       0