Oracle date / time calcuation !

[Chris Fung]

Hi there,

I am just thinking of creating a statistic table to keep track the start time, end time and duration of the batch run. However, it seems I cannot calcuate and display the duration in correct format (HH:MM:SS or just in minutes)

Table script:
create table stat
( batch_name char(35),
status char(12),
start_time date,
end_time date,
duration date);

When the batch start, a record will be inserted to the table:

insert into stat (batch_name, status, start_time) values ('batch a', 'progressing', sysdate);

When the process end, I am trying to update the same record but I don't know how to display the duration correctly....

update stat set stat='completed', end_time=sysdate, duration=end_time - start_time where process_name='batch a';

Any idea ? Appreciated for your inputs !!

Cheers,

Chris,

[twang]

Oracle returns the difference in integer number of days and decimal portion of day. This value must be converted manually to be displayed in the format desired.
You must use PL/SQL or other program code to display date differences in the format Years, Months, Days, Hours, Minutes, Seconds.
The following example loads a table with 2 records with 2 date values for starting and ending dates. The pl/sql block will calculate the difference of the dates and display the result in the format described above. create table ddiff (stdate date, endate date); insert into ddiff (stdate,endate) values (to_date('01-jan-96 00:00:00','dd-mon-yy hh24:mi:ss'), to_date('01-mar-97 01:01:01','dd-mon-yy hh24:mi:ss')); insert into ddiff (stdate,endate) values (to_date('01-jun-99 00:00:00','dd-mon-yy hh24:mi:ss'), to_date('03-oct-99 02:02:02','dd-mon-yy hh24:mi:ss')); set echo off set serverout on declare cursor c1 is select stdate,endate from ddiff; date_res number; end_now date; f_end_now char(20); st_now date; temp_now date; f_st_now char(20); yy_now number := 0; mm_now number := 0; dd_now number := 0; hh_now number := 0; f_hh_now number := 0; mi_now number := 0; f_mi_now number := 0; ss_now number := 0; f_ss_now number := 0; begin open c1; loop fetch c1 into st_now, end_now; exit when c1%notfound; -- Make st_now the earlier date if (end_now < st_now) then temp_now := end_now; end_now := st_now; st_now := temp_now; end if; f_end_now := to_char(end_now,'Mon-dd-yyyy hh24:mi:ss'); f_st_now := to_char(st_now,'Mon-dd-yyyy hh24:mi:ss'); yy_now := trunc(months_between(end_now,st_now)/12); mm_now := mod(trunc(months_between(end_now,st_now)),12); dd_now := trunc(end_now-add_months(st_now,months_between(end_now,st_now))); date_res := (end_now-st_now); hh_now := (date_res-floor(date_res))*24; f_hh_now := trunc(hh_now); mi_now := (hh_now-floor(hh_now))*60; f_mi_now := trunc(mi_now); ss_now := (mi_now-floor(mi_now))*60; f_ss_now := round(ss_now); dbms_output.put_line(chr(10)|| 'The time difference between '); dbms_output.put_line('starting date '||f_st_now); dbms_output.put_line('and ending date '|| f_end_now||' is'); dbms_output.put_line(chr(10)||yy_now||' year(s), '|| mm_now||' month(s), '||dd_now||' day(s)'); dbms_output.put_line('and '||f_hh_now||' hour(s), '|| f_mi_now||' minute(s), '|| f_ss_now||' second(s)'); end loop; close c1; end; /

[twang]

repost the sample script.

[Yogeeraj_1]

hi,

we use quite a different approach to timing procedure execution. We calculate the processing time in the procedure itself and store it as numbers.

Example.
yd@MYDB.MU> DECLARE
2 v_hsec PLS_INTEGER := DBMS_UTILITY.get_time;
3 BEGIN
4 dbms_lock.sleep( 2 );
5 DBMS_OUTPUT.put_line ('Procedure 1 sec = ' || (DBMS_UTILITY.get_time - v_hsec) / 100);
6* END;
Procedure 1 sec = 2.05

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.09
yd@MYDB.MU>


hence, you can just calculate the value and store it in the table column at the end of the procedure execution.

hope this helps!

regards
Yogeeraj