Convert INTERVAL with milliseconds to an integer

[cswords]

I need to calculate the time in milliseconds between two events and then display the results in Crystal Reports.

The obvious solution is to use datatype TIMESTAMP and INTERVAL; however, Crystal Reports does not support either type. So, I was thinking that I would create a view with the difference displayed as an integer.... only I have NO IDEA how to do this!

The difference needs to be displayed with milliseconds and as I need to perform calculations on the difference (i.e. min, max, average etc), I need it as a number/integer verses a character string.

Any ideas?

 

[SantaMufasa]

CS,

Here is a function that translates the difference between two timestamps into an integer result:

create or replace function to_millisecs (time_in varchar2) return number is
        ms    number;
        secs  number;
        mins  number;
        hrs   number;
        days  number;
        tot   number;
begin
    if substr(time_in,11,1) = ' ' and
       substr(time_in,14,1) = ':' and
       substr(time_in,17,1) = ':' and
       substr(time_in,20,1) = '.' then
        ms    := substr(time_in,21,3);
        secs  := substr(time_in,18,2)*1000;
        mins  := substr(time_in,15,2)*60*1000;
        hrs   := substr(time_in,12,2)*60*60*1000;
        days  := substr(time_in,2,9)*24*60*60*1000;
        tot   := ms+secs+mins+hrs+days;
        if substr(time_in,1,1)='-' then
            tot:=tot*-1;
        end if;
        return tot;
    end if;
end;
/

Function created.

col a format a26
col b like a
col c heading "Difference|In|Milliseconds" format 999,999,999,999
select a,b,to_millisecs(b-a)c from times;

                                                            Difference
                                                                    In
A                          B                              Milliseconds
-------------------------- -------------------------- ----------------
10-MAR-05 12.09.55.5730 PM 10-MAR-05 12.47.47.4100 PM        2,271,837
10-MAR-05 12.48.19.0150 PM 11-MAR-05 12.48.19.0000 PM       86,399,985
10-MAR-05 12.57.55.6240 PM 18-JUN-05 12.57.55.0000 PM    8,639,999,376
**********************************************************************

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.