3

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
GMB
  • 216,147
  • 25
  • 84
  • 135
MBasith
  • 1,407
  • 4
  • 29
  • 48

2 Answers2

4

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
GMB
  • 216,147
  • 25
  • 84
  • 135
0

You could take a look at extract() Checkout this answer: TIMESTAMPDIFF in Oracle 11g?

wren
  • 41
  • 3