Average time between SQL arrivals, time between dates

Question

I have a table with consecutive 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 them (maybe tens) in seconds or something simple, I can work with him from there. So, for example, the indicated time between arrivals only for the first two times would be 870 (14 m 30 s). For all three times, this would be: (870 + 120) / 2 = 445 (7 m 25 s).

Note, I am using postgreSQL 8.1.22.

EDIT: the table I mentioned above refers to another query, which is literally just one columnar list of timestamps

Answer 1

, , , :

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

. .

.

Answer 2

, avg (timestamptz).

- avg ( - ). - , min.

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

Answer 3

PG, , " ".

, "tbl", timestamp - "ts":

SELECT AVG(t1 - t0)
  FROM (
        -- All this silliness would be moot if we could use
        -- `` lead(ts) over (order by ts) ''
        SELECT tbl.ts  AS t0,
               next.ts AS t1
          FROM tbl
         CROSS JOIN
               tbl next
         WHERE next.ts = (
                          SELECT MIN(ts)
                            FROM tbl subquery
                           WHERE subquery.ts > tbl.ts
                         )
       ) derived;

. . , a_horse_with_no_name .