Get the delta time between two rows in MySQL using SQL

Question

I have a query

select 
    event_time, argument 
from 
    mysql.general_log 
where 
    argument <> 'SHOW WARNINGS' 
order by 
    event_time;

and I was wondering if it is possible to add a column that would say what the time diff between rows would be for each row?

Similar to Getting the Delta between the rows on SQL and time difference between two rows - mysql (uses PHP) by for using only SQL in MySQL (no PHP)

Answer 1

You can use lag():

select event_time, argument,
       timestampdiff(second, lag(event_time) over (order by event_time), event_time) as diff
from mysql.general_log
where argument <> 'SHOW WARNINGS'
order by event_time ;

In older versions of MySQL, you can use:

select gl.event_time, gl.argument,
       timestampdiff(second,
                     (select gl2.event_time
                      from mysql.general_log gl2
                      where gl2.event_time < gl.event_time
                      order by gl2.event_time desc
                      limit 1
                    ) as diff
from mysql.general_log gl
where gl.argument <> 'SHOW WARNINGS'
order by gl.event_time ;

Answer 2

Marking as duplicate, but I'll put the answer here in case someone wants to do a similar analysis for mysql.general_log

select argument, event_time, timestampdiff( MICROSECOND ,prevdatenew, event_time) as diff
from (
    select argument, event_time, @prevDateNew as prevdatenew, 
      @prevDateNew := event_time
      from mysql.general_log
    where argument <> 'SHOW WARNINGS'
    order by event_time 
) t1
where argument not like 'SET auto%' and argument not like '/*%'
-- order by diff desc