Home » SQL & PL/SQL » SQL & PL/SQL » Calculated Fields and formatting
Calculated Fields and formatting [message #231056] Fri, 13 April 2007 16:29 Go to next message
anne_marie_oregon
Messages: 1
Registered: April 2007
Junior Member
Hello, I would like to create an elapsed time field in my query. For example, I would like to display the start_time the stop_time and the elapsed. I tried this:

to_date((stop_time - start_time), 'HH:MI:SS')

but that is not working.

when I simply say,

select start_time, stop_time, (stop_time - start_time)
from job

I am using TOAD, and it displays as this:

0.0493171296296296

That isn't useful to me! Smile

So does anyone know how I can get the right format? Thank you!
Re: Calculated Fields and formatting [message #231057 is a reply to message #231056] Fri, 13 April 2007 16:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>So does anyone know how I can get the right format?
May be I missed it, but EXACTLY what is the "right format"?
I suggest you take a close, close, close look at TO_CHAR function
Re: Calculated Fields and formatting [message #231063 is a reply to message #231056] Fri, 13 April 2007 19:31 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Here's some code that should help ...

set linesize 120
set scan off
set feedback off

column value format a21

select value from nls_session_parameters
where parameter = 'NLS_TIMESTAMP_FORMAT' ;

VALUE
---------------------
YYYY-MM-DD HH24:MI:SS

create table t ( start_time timestamp, stop_time timestamp );
insert into t values
  ( timestamp '2007-01-01 00:00:00', timestamp '2007-01-01 06:00:00' );
insert into t values
  ( timestamp '2007-01-01 00:00:00', timestamp '2007-01-01 12:00:00' );
insert into t values
  ( timestamp '2007-01-01 00:00:00', timestamp '2007-01-01 12:34:56' );
insert into t values
  ( timestamp '2007-01-01 00:00:00', timestamp '2007-01-03 12:34:56' );
commit;

column start_time         format a20
column stop_time          format a20
column elapsed_time       format a26
column elapsed_hh_mi_ss_1 format a20
column elapsed_hh_mi_ss_2 format a20

select
  start_time,
  stop_time,
  stop_time - start_time as elapsed_time ,
  cast(stop_time - start_time as interval day(2) to second(0) )
    as elapsed_hh_mi_ss_1,
  lpad(extract( HOUR   from stop_time - start_time ),2,'0' ) || ':' ||
  lpad(extract( MINUTE from stop_time - start_time ),2,'0' ) || ':' ||
  lpad(extract( SECOND from stop_time - start_time ),2,'0' )
  as elapsed_hh_mi_ss_2
from t ;

START_TIME           STOP_TIME            ELAPSED_TIME               ELAPSED_HH_MI_SS_1   ELAPSED_HH_MI_SS_2
-------------------- -------------------- -------------------------- -------------------- --------------------
2007-01-01 00:00:00  2007-01-01 06:00:00  +000000000 06:00:00.000000 +00 06:00:00         06:00:00
2007-01-01 00:00:00  2007-01-01 12:00:00  +000000000 12:00:00.000000 +00 12:00:00         12:00:00
2007-01-01 00:00:00  2007-01-01 12:34:56  +000000000 12:34:56.000000 +00 12:34:56         12:34:56
2007-01-01 00:00:00  2007-01-03 12:34:56  +000000002 12:34:56.000000 +02 12:34:56         12:34:56

drop table t;


--
Joe Fuda
http://www.sqlsnippets.com/
Re: Calculated Fields and formatting [message #231064 is a reply to message #231063] Fri, 13 April 2007 21:27 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
BTW, the above examples assume you can change the datatype of your database table columns from DATE to TIMESTAMP. If you don't have that luxury then consider one of the following approaches.
create table t2 ( start_time date, stop_time date );
insert into t2 values( date '2007-01-01', date '2007-01-01' + 0.25 );
insert into t2 values( date '2007-01-01', date '2007-01-01' + 0.5  );
insert into t2 values( date '2007-01-01', to_date( '2007-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS' ) );
insert into t2 values( date '2007-01-01', to_date( '2007-01-03 12:34:56', 'YYYY-MM-DD HH24:MI:SS' ) );
commit;

column start_time         format a20
column stop_time          format a20
column elapsed_time       format a29
column elapsed_hh_mi_ss_1 format a20
column elapsed_hh_mi_ss_2 format a20

select
  start_time,
  stop_time,
  stop_time - start_time as elapsed_time ,
  cast
  ( stop_time - start_time as interval day(2) to second(0) )
     as elapsed_hh_mi_ss_1,
  lpad(extract( HOUR   from stop_time - start_time ),2,'0' ) || ':' ||
    lpad(extract( MINUTE from stop_time - start_time ),2,'0' ) || ':' ||
    lpad(extract( SECOND from stop_time - start_time ),2,'0' )
    as elapsed_hh_mi_ss_2
from
  ( select
      cast( start_time as timestamp ) start_time,
      cast( stop_time  as timestamp ) stop_time
    from t2
  )
;

START_TIME           STOP_TIME            ELAPSED_TIME                  ELAPSED_HH_MI_SS_1   ELAPSED_HH_MI_SS_2
-------------------- -------------------- ----------------------------- -------------------- --------------------
2007-01-01 00:00:00  2007-01-01 06:00:00  +000000000 06:00:00.000000    +00 06:00:00         06:00:00
2007-01-01 00:00:00  2007-01-01 12:00:00  +000000000 12:00:00.000000    +00 12:00:00         12:00:00
2007-01-01 00:00:00  2007-01-01 12:34:56  +000000000 12:34:56.000000    +00 12:34:56         12:34:56
2007-01-01 00:00:00  2007-01-03 12:34:56  +000000002 12:34:56.000000    +02 12:34:56         12:34:56

select
  to_char( start_time, 'YYYY-MM-DD HH24:MI:SS' ) as start_time ,
  to_char( stop_time, 'YYYY-MM-DD HH24:MI:SS' ) as stop_time ,
  stop_time - start_time as elapsed_days ,
  numtodsinterval(stop_time - start_time, 'DAY') as elapsed_time ,
  cast
  ( numtodsinterval(stop_time - start_time, 'DAY')
    as interval day(2) to second(0)
  ) as elapsed_hh_mi_ss_1
from t2 ;

START_TIME           STOP_TIME            ELAPSED_DAYS ELAPSED_TIME                  ELAPSED_HH_MI_SS_1
-------------------- -------------------- ------------ ----------------------------- --------------------
2007-01-01 00:00:00  2007-01-01 06:00:00           .25 +000000000 06:00:00.000000000 +00 06:00:00
2007-01-01 00:00:00  2007-01-01 12:00:00            .5 +000000000 12:00:00.000000000 +00 12:00:00
2007-01-01 00:00:00  2007-01-01 12:34:56    .524259259 +000000000 12:34:55.999999999 +00 12:34:56
2007-01-01 00:00:00  2007-01-03 12:34:56    2.52425926 +000000002 12:34:56.000000000 +02 12:34:56

Previous Topic: How can I create a column after a particular column
Next Topic: EVEN IT IS STATEMENT LEVEL TRIGGER WHY IT IS FIRED 10 TIMES?
Goto Forum:
  


Current Time: Sun Dec 04 04:44:21 CST 2016

Total time taken to generate the page: 0.21511 seconds