Calculate time difference between log-entries

by

For me, myself and I (as a reminder) 🙂

I needed to calculate the difference between a log entry and the previous log-entry to be able to investigate where performance was dropping:

With alittle bit of help here and here I figured out how to do it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
with iv
as  (select PROGRAM
     ,      RUN_ID
     ,      PAL_N_LEVEL
     ,      MESSAGE
     ,      DATE_TIME
     ,      LOG_D_DATE
     ,      case
              when message like 'Start%' then ''
              when message like 'End%' then ''
              else substr(to_char(numtodsinterval(LOG_D_DATE - lag(LOG_D_DATE, 1, sysdate) over (order by LOG_D_DATE asc), 'day')), 15, 5)
            end time_spent
     from   vla
     where  run_id = 264225
     order  by log_d_date asc)
select PROGRAM
,      RUN_ID
,      PAL_N_LEVEL
,      MESSAGE
,      DATE_TIME
,      LOG_D_DATE
,      TIME_SPENT "Time spent (mm:ss)"
from   iv
where  1=1
  and  (1=0
        or message like 'refreshed%'
--        or message like 'refresh%'
        or message like 'Start%'
        or message like 'End%'
       )

Leave a Comment