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:

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