Home » SQL & PL/SQL » SQL & PL/SQL » Calculated Fields and formatting
| Calculated Fields and formatting [message #231056] |
Fri, 13 April 2007 16:29  |
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! 
So does anyone know how I can get the right format? Thank you!
|
|
|
|
|
|
| Re: Calculated Fields and formatting [message #231063 is a reply to message #231056] |
Fri, 13 April 2007 19:31  |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Sun Dec 08 07:43:23 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
