Calculate time difference between log-entries

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

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%'
       )