Edit the answer that is returned by my select

Question

I have a selection to find the difference between two dates

select  SUBSTR(to_timestamp('19.09.2019 11:26:00', 'dd.mm.yyyy hh24:mi:ss' ) - to_timestamp('01.01.2019 00:00:00' , 'dd.mm.yyyy hh24:mi:ss' ),8, 12) diff 
from dual

The answer I teach is correct

261 11:26:00

Is it possible to somehow get the answer of this format

261 day 11 hour 26 min 00 sec

You need something like this: https://stackoverflow.com/a/970461 — , Oct 07 '19 at 12:04
Possible duplicate of [format interval with to\_char](https://stackoverflow.com/questions/970249/format-interval-with-to-char) — Radagast81, Oct 07 '19 at 12:38

score 2 · Accepted Answer · answered Oct 07 '19 at 12:58

Use EXTRACT and string concatenation:

SELECT EXTRACT( DAY FROM diff ) || ' days'
       || ' ' || LPAD( EXTRACT( HOUR   FROM diff ), 2, '0' ) || ' hours'
       || ' ' || LPAD( EXTRACT( MINUTE FROM diff ), 2, '0' ) || ' minutes'
       || ' ' || LPAD( EXTRACT( SECOND FROM diff ), 2, '0' ) || ' seconds'
         AS diff
FROM   (
  SELECT TIMESTAMP '2019-09-19 11:26:00' - TIMESTAMP '2019-01-01 00:00:00'
           AS diff 
  FROM   DUAL
)

Outputs:

| DIFF                                    |
| :-------------------------------------- |
| 261 days 11 hours 26 minutes 00 seconds |

db<>fiddle here

Edit the answer that is returned by my select

1 Answers1