How to format time invervals?

[Lothar Armbrüster]

Hello out there,
I'm trying to format some datetime invervals:

select
to_char(numtodsinterval(elapsed_seconds,'SECOND'),'DDD HH24:MI:SS')
from
v$session_longops where rownum<10;

But I always get the default format back:

TO_CHAR(NUMTODSINTERVAL(ELAPSE
------------------------------
+000000000 00:00:15.000000000
+000000000 00:00:16.000000000
+000000000 00:04:58.000000000
+000000000 00:00:18.000000000
+000000000 00:05:42.000000000
+000000000 00:00:16.000000000
+000000000 00:00:17.000000000
+000000000 00:00:18.000000000
+000000000 00:00:39.000000000

The Oracle docs don't give any hint how to format these intervals.
Obviously, the format models for the date datatype don't work for intervals.

Can anyone tell me how to do it right?

I use Oracle 9.0.1.3.1 on W2k.

Many thanks in advance,
Lothar

[Kay Kanekowski]

Hallo Lothar,

l.armb...@vertriebsunion.de (Lothar Armbrüster) wrote in message news:<35559896.03033...@posting.google.com>...

> Hello out there,
> I'm trying to format some datetime invervals:
>
> select
> to_char(numtodsinterval(elapsed_seconds,'SECOND'),'DDD HH24:MI:SS')
> from
> v$session_longops where rownum<10;
>

> The Oracle docs don't give any hint how to format these intervals.

> Obviously, the format models for the date datatype don't work for intervals.
>

i think you can't format the result of numtodsinterval, it is a varchar.
The only way i see is something like that

substr(numtodsinterval(elapsed_seconds,'SECOND'),12, 8)
or
substr(numtodsinterval(elapsed_seconds,'SECOND'), 8, 12)

Then you get "hh24:mi:ss" or "ddd hh24:mi:ss".

hth
Kay

[Webster Joe]

Kay Kanekowski wrote:
> i think you can't format the result of numtodsinterval, it is a
varchar.

According to the "Oracle Database SQL Reference 10g" manual,
numtodsinterval returns an INTERVAL DAY TO SECOND literal,
not a varchar.

> l.armb...@vertriebsunion.de (Lothar Armbrüster) wrote

> > I'm trying to format some datetime invervals:
> >
> > select
> > to_char(numtodsinterval(elapsed_seconds,'SECOND'),'DDD
HH24:MI:SS')
> > from
> > v$session_longops where rownum<10;
> >

> > But I always get the default format back:

Try one of the two different techniques demonstrated in the
following SELECT statement.

select
to_char( cast(elapsed_interval as interval day(3) to second(0)) )
as elapsed_seconds_method_1,
to_char
( lpad(extract(DAY from elapsed_interval),3,'0')||' '||
lpad(extract(HOUR from elapsed_interval),2,'0')||':'||
lpad(extract(MINUTE from elapsed_interval),2,'0')||':'||
lpad(extract(SECOND from elapsed_interval),2,'0')
) as elapsed_seconds_method_2
from
( select numtodsinterval(elapsed_seconds,'SECOND') elapsed_interval

from v$session_longops
where rownum < 10

)
;

ELAPSED_SECONDS_METHOD_1 ELAPSED_SECONDS_METHOD_2
------------------------ ------------------------
+000 00:00:26 000 00:00:26
+000 00:00:22 000 00:00:22
+000 00:01:10 000 00:01:10
+000 00:00:24 000 00:00:24
+000 00:00:23 000 00:00:23
+000 00:00:23 000 00:00:23
+000 00:00:30 000 00:00:30
+000 00:00:27 000 00:00:27
+000 00:00:07 000 00:00:07

Method 1 is more compact, but Method 2 gives you more control
over the output format.