Find average row-to-row time interval

from the Artful Common Queries page

You have a table of sequential times, and you're asked to report the average time interval between them in seconds. Assuming table t with timestamp column ts and no ts duplicate values, the mean time difference between consecutive rows is the difference between the smallest (earliest) and largest (latest) timestamp divided by the rowcount - 1: 
Select TimeStampDiff(SECOND, Min(ts), Max(ts) ) / ( Count(DISTINCT(ts)) -1 ) 
FROM t
Not sure of the formula?

1. The mean distance between consecutive rows is the sum of distances between consecutive rows, divided by the number of consecutive rows.

2. The sum of differences between consecutive rows is just the distance between the first row and last sorted row (assuming they are sorted by timestamp).

3. The number of consecutive rows is the total number of rows - 1.

Last updated 20 Jun 2024