Simple use of lag function to show script runtimes

This is a simple script to show how long each script runs for in a table where all that is logged is the start_time of the script to an update_log table.

The out put is the script name and how long it took by being able to refer directly to the date of the previous row creation - hence giving the difference in start times which equates to the run time.

select *

  from (

       

        select script_name,

                (row_creation - prev_row_creation) * (24 * 60 * 60) runtime_secs

       

          from (select SCRIPT_NAME,

                        ROW_CREATION,

                        lag(row_creation, 1) over(order by row_creation) prev_row_creation

                   from update_log

                  where row_creation > trunc(sysdate))

       

        )

 where runtime_secs > 60