Converting INTERVAL DAY TO SECOND

Question

I want to get more readable value using interval data type.

select cast(last_run_duration as INTERVAL DAY TO SECOND(0))
from dba_scheduler_jobs
order by 1 desc;

Query result like this;

+00 00:00:04.630530
+00 00:00:01.352359
+00 00:00:00.737282
+00 00:00:00.594381
+00 00:00:00.432710

What i want is this. Only getting Hour:minute:second ;

 00:00:04
 00:00:01
 00:00:00
 00:00:00
 00:00:00

last_run_duration column's datatype is INTERVAL DAY(9) TO SECOND(6)

Not an exact duplicate maybe, but [the second half of this answer](https://stackoverflow.com/a/45601629/266304) shows some options. — Alex Poole, Jan 25 '18 at 13:37
@jarlh `cast(last_run_duration as interval hour to second(0)` gives me _ORA-00963: unsupported interval type_ in 12.1.0.2.0. — William Robertson, Jan 25 '18 at 15:32
Your query gives me values like `+00 01:26:24` in SQL*Plus (Oracle 12.1.0.2.0, client 11.2.0.2.0). Casting to `INTERVAL DAY(0) TO SECOND(0))` loses one of the leading zeroes. To strip those you can use `substr` if you know it will never be more than a day. If you want something like `36:12:34` that's going to be trickier. Is that possible in your situation? — William Robertson, Jan 25 '18 at 15:56

Wernfried Domscheit · Accepted Answer · 2018-01-25T13:53:47.450

INTERAVL DAY TO SECOND has a fixed output format, TO_CHAR does not work.

Either use EXTRACT as proposed by Edumelzer

select lpad(extract(hour from last_run_duration),2,'0')||':'||
       lpad(extract(minute from last_run_duration),2,'0')||':'||
       lpad(round(extract(second from last_run_duration)),2,'0') as duration
  from dba_scheduler_jobs
 order by 1 desc;

or use substring like

select 
REGEXP_SUBSTR(last_run_duration, '\d{2}:\d{2}:\d{2}')
from from dba_scheduler_jobs;

score 2 · Answer 2 · answered Jan 25 '18 at 13:43

2

Try using extract:

select lpad(extract(hour from last_run_duration),2,'0')||':'||
       lpad(extract(minute from last_run_duration),2,'0')||':'||
       lpad(round(extract(second from last_run_duration)),2,'0') as duration
  from dba_scheduler_jobs
 order by 1 desc;

answered Jan 25 '18 at 13:43

Edumelzer

1,066
1
11
22

2

In case the `day` component could ever be > 0, you might also extract `day`, multiply by 24 and add to `hour`, to give 1.5 days as `36:00:00`. – William Robertson Jan 25 '18 at 16:04

Converting INTERVAL DAY TO SECOND

2 Answers2