CODEWITHSUNDEEP
sqlpostgresql
Yottagray
Yottagray

Reputation: 2592

SQL Average Inter-arrival Time, Time Between Dates

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

Upvotes: 7

Views: 6633

Answers (3)

pilcrow
pilcrow

Reputation: 58619

If you cannot upgrade to a version of PG that supports window functions, you may compute your table's sequential steps "the slow way."

Assuming your table is "tbl" and your timestamp column is "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;

But don't do that. Its performance will be terrible. Please do what a_horse_with_no_name suggests, and use window functions.

Upvotes: 1

Bonshington
Bonshington

Reputation: 4032

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

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 19

Related Questions