SQL Average Inter-arrival Time, Time Between Dates

Question

I have a table with sequential timestamps:

2011-03-17 10:31:19

2011-03-17 10:45:49

2011-03-17 10:47:49 ...

I need to find the average time difference between each of these(there could be dozens) in seconds or whatever is easiest, I can work with it from there. So for example the above inter-arrival time for only the first two times would be 870 (14m 30s). For all three times it would be: (870 + 120)/2 = 445 (7m 25s).

A note, I am using postgreSQL 8.1.22 .

EDIT: The table I mention above is from a different query that is literally just a one-column list of timestamps

Answer 1

Not sure I understood your question completely, but this might be what you are looking for:

SELECT avg(difference)
FROM ( 
  SELECT timestamp_col - lag(timestamp_col) over (order by timestamp_col) as difference
  FROM your_table
) t

The inner query calculates the distance between each row and the preceding row. The result is an interval for each row in the table.

The outer query simply does an average over all differences.

Answer 2

i think u want to find avg(timestamptz).

my solution is avg(current - min value). but since result is interval, so add it to min value again.

SELECT  avg(target_col - (select min(target_col) from your_table))
        + (select min(target_col) from your_table)
FROM    your_table