How to Find the Average Time Difference Between Rows in a Table

How to find the average time difference between rows in a table?

If your table is t, and your timestamp column is ts, and you want the answer in seconds:

SELECT TIMESTAMPDIFF(SECOND, MIN(ts), MAX(ts) ) 
       /
       (COUNT(DISTINCT(ts)) -1) 
FROM t

This will be miles quicker for large tables as it has no n-squared JOIN

This uses a cute mathematical trick which helps with this problem. Ignore the problem of duplicates for the moment. The average time difference between consecutive rows is the difference between the first timestamp and the last timestamp, divided by the number of rows -1.

Proof: The average distance between consecutive rows is the sum of the distance between consective rows, divided by the number of consecutive rows. But the sum of the difference between consecutive rows is just the distance between the first row and last row (assuming they are sorted by timestamp). And the number of consecutive rows is the total number of rows -1.

Then we just condition the timestamps to be distinct.

How to calculate average time difference in 2 columns in the given table?

You can just take the difference and average:

select avg(datetime2 - datetime1)
from t;

avg() ignores NULL values.

Here is a db<>fiddle.

Get average time difference between current and previous row

Assuming you're using MySQL 8 or later you can use lag function to find the previous datetime in order to calculate the difference. Rest is straight forward:

with cte as (
    select cast(ts as date) as dt
         , ts
         , lag(ts) over(partition by cast(ts as date) order by ts) as prev_ts
    from t
)
select dt
     , count(*) as count
     , avg(timestampdiff(second, prev_ts, ts)) as avg_diff
from cte
group by dt

Calculate average of time difference between consecutive row

We can use DATEDIFF along with LAG:

WITH cte AS (
    SELECT tran_end_time,
           LAG(tran_end_time) OVER (ORDER BY tran_end_time) AS tran_end_time_lag
    FROM yourTable
)

SELECT AVG(DATEDIFF(minute, tran_end_time_lag, tran_end_time)) AS diff_avg
FROM cte
WHERE tran_end_time_lag IS NOT NULL;

Note that the WHERE clause in the final query above ensures that we do not include any diff involving the earliest record.

Get average time difference grouped by another column in postgresql

You can use aggregation and arithmetic:

select post_author,
       (max(post_date) - min(post_date)) / nullif(count(*) - 1, 0)
from t
group by post_author;

The average days is the difference between the maximum and minimum days, divided by one less than the count.

Average time difference by rows with different column value

While MySQL allows some weird combinations of columns between SELECT and GROUP BY clauses, the general rule for standard SQL is "when using aggregation functions, all columns in your SELECT must be either aggregated or placed in GROUP BY too".

What you're trying to do then requires a query like this

select  MO, PiID, Part_Number,
        count(*) as CNT,
        TIMESTAMPDIFF(SECOND,
                      min(Timestamp),
                      max(Timestamp)
                     ) / (count(*) - 1) as Average_Time
from    datalog
group by MO, PiID, Part_Number
having  count(*) > 1

This will return the average time in seconds, but you can use different time units as well.

Note that calculating the average time difference requires at least two timestamps to be available, so I added that HAVING clause. Without that, MO / PiID combinations with only one row would cause a division by zero.

Edit

After thinking about it better, actually you don't need the HAVING clause, because if a MO / PiID combination has 1 row, the min and max will be the same, hence the difference will be 0 and the result of that query will be 0 / 0, which MySQL translates into NULL.

If you prefer something else to be displayed instead of NULL, you can use a coalesce function

select  MO, PiID, Part_Number,
        count(*) as CNT,
        TIMESTAMPDIFF(SECOND,
                      min(Timestamp),
                      max(Timestamp)
                     ) / (count(*) - 1) as Average_Time,
        COALESCE(TIMESTAMPDIFF(SECOND,
                               min(Timestamp),
                               max(Timestamp)
                              ) / (count(*) - 1),
                'something_else') as Average_Time_Coalesced
from    datalog
group by MO, PiID, Part_Number

Calculate Time Difference Between Two Rows

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
        FROM    mytable
        )
SELECT  DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

In SQL Server 2012+:

SELECT  DATEDIFF(second, pDataDate, dataDate)
FROM    (
        SELECT  *,
                LAG(dataDate) OVER (ORDER BY dataDate) pDataDate
        FROM    rows
        ) q
WHERE   pDataDate IS NOT NULL

---

Related Topics