Re: How to format time invervals?

From: Webster Joe <webster.joe_at_gmail.com>
Date: 16 Feb 2005 11:04:05 -0800
Message-ID: <1108580645.036596.278970@g14g2000cwa.googlegroups.com>

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.armbruester_at_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. Received on Wed Feb 16 2005 - 13:04:05 CST