Re: how do u compare timestamps on different rows that are related to same event

From: Romeo Olympia <rolympia_at_hotmail.com>
Date: 9 Jun 2004 11:44:35 -0700
Message-ID: <42fc55dc.0406091044.228044b7@posting.google.com>

This for kicks mostly. Not fully tested and with lots of assumptions/restrictions.
- Each event class (ie. web login) starts with 'start' record and is
followed by 'end' record for that same event class. Basically no overlapping batch runs. Other event classes that come in between are allowed though.
- Would be a lot easier and reliable if there were a batch_id of some
sort. Something to identify that this 'start' record belongs to that 'end record'.
- Might be better in PL/SQL. Depends, don't wanna think about that
one.

Here's the SQL. Two inline views. Inner-most just 'parses' the event_name into an event_class and an event_flag. Other inline view is the usual analytics LEAD function. Converted the duration to an interval datatype.

col event_start format a20
col event_end format a20
col duration format a30

select event_name as event_start,
next_event as event_end,
numtodsinterval(next_time - event_time, 'day') as duration from
(

   select a.*,
   lead(event_time) over (partition by event_class order by event_time) as next_time,

   lead(event_name) over (partition by event_class order by event_time) as next_event

   from
   (

      select event_name, 
      case when instr(event_name, 'start') > 0 then
            substr(event_name, 1,instr(event_name, 'start') -1)
           when instr(event_name, 'end') > 0 then
           substr(event_name, 1,instr(event_name, 'end') -1)   
      end as event_class,
      case when instr(event_name, 'start') > 0 then
            substr(event_name, instr(event_name, 'start'))
           when instr(event_name, 'end') > 0 then
            substr(event_name, instr(event_name, 'end'))   
      end as event_flag,
      event_time
      from jevents

   ) a
)
where event_flag = 'start'
and next_event like '%end'
order by event_time

EVENT_START EVENT_END DURATION

-------------------- --------------------
------------------------------
web login start      web login end        +000000000
01:25:08.000000000
web update start     web update end       +000000000
00:04:04.000000000
web insert start     web insert end       +000000000
00:10:59.000000000
web login start      web login end        +000000000
00:35:07.999999999
web update start     web update end       +000000000
00:06:04.000000000
web insert start     web insert end       +000000000

00:10:59.000000000

Cheers.

sdfdfwetudfyt_at_7fdfster.com (hourman) wrote in message news:<40c65f71$0$52008$45beb828_at_newscene.com>...

> Oracle 9i  on Sun Unix on Sun Star server
> 
> a batch process runs every 6 hours.  its composes of 10 stored procedures that 
> run one after another assuming prior one finished ok.  each procedure logs 
> when it started and when it ended.  total takes 20-30 minutes but sometimes 
> 1-2 hours.
> 
> we have following table
> 
> event name                      date
> 
> web login start                 7/1/04 9:24:14
> web update start                      7/1/04 9:24:36
> web update end                      7/1/04 9:28:40
> web insert start                       7/1/04 10:34:15
> web insert end                      7/1/04 10:45:14
> web login end                       7/1/04 10:49:22
> 
> web login start                 7/1/04 15:24:14
> web update start                      7/1/04 15:25:36 ....
> 
> what we want to see is following
> 
> event start             event end               total time
> 
> web login start         web login end       1:25:08
> webupdate start        web update end    0:4:23  etc
> .
> 
> and 2nd batch 6 hours later
> 
> web login start         web login end       1:25:08
> webupdate start        web update end    0:4:23  etc
> 
> 
> we want the ones related to each other in each batch 
> 
> is this possible?

Received on Wed Jun 09 2004 - 13:44:35 CDT