Calculate time between incidents

tstimple

Team,

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

etc...

average of all

 

Help please?

Tim

 

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`))

MarkSnodgrass

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. 

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...

 

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

 Ideas...?