Calculate time between incidents



I am a newbie, thanks for helping

I have data in a table that contains a timestamp of incident creation time on each row.

I am trying to show the average time between occurrences. 

Basically, I want to write a calculation that with give me the average time that have passed since the previous occurrence (row)


I know this is wrong, but Something like...?

(Row 2 'sys_create_time') - (Row 1 'sys_create_time')

(Row 3 'sys_create_time') - (Row 2 'sys_create_time')


average of all


Help please?




  • GrantSmith

    Hi @tstimple 


    You can use DATE_DIFF or some math with UNIX_TIMESTAMP (returns the number of seconds as an integer since 1970-01-01 which I prefer as I've had some odd results with date diff.)


    It appears you've got a single data column 


    Assuming you're doing this in a beast mode it'd look something like this:

    UNIX_TIMESTAMP(`sys_create_time`) - LAG(UNIX_TIMESTAMP(`sys_create_time`)) OVER (ORDER BY `sys_create_time`)


    LAG is a window function which allows you to grab the prior row. You'll need to talk with your CSM to get it turned on in your instance if you don't have it yet.

    LEAD is similar but it grabs the next row (goes the opposite direction).


    This will return the difference in time in seconds. If you want it in other units like minutes / hours / days then you can do some simple division.


    To wrap it all up in a nice bow you can take the AVG of the difference to get the average.

    AVG(UNIX_TIMESTAMP(`sys_create_time`) - LAG(UNIX_TIMESTAMP(`sys_create_time`)) OVER (ORDER BY `sys_create_time`))
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • I would take what @GrantSmith  is suggesting, but do it in Magic ETL because it already supports the window function and you don't need to talk toy our CSM. 

    Just use the Rank & Window tile and use the LAG function as suggested so that your difference between occurrences will show as another column. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • tstimple

    Thanks for the reply re LAG/LEAD functions.

    Apparently those are already turned on in our system.


    So, using your formula without the 'AVG' wrapper works to populate a column, BUT I cannot get it to aggregate.

    If I add the AVG wrapper it breaks the function.


    If I use it without the AVG, I can get it to show in a table view...

    DOMO Lag Time.png

    But I cannot get it to aggregate as a single number to give me average of all the rows...

    DOMO error.png