SQL - Query to return result

Question

There is a table with Columns as below:

Id : long autoincrement; 
timestamp:long; 
price:long

Timestamp is given as a unix_time in ms.

Question: what is the average time difference between the records ?

Answer 1

At a guess:

SELECT AVG(timestamp)

I think you need to provide more information in your question for us to help.

Answer 2

If you mean difference between each-other row:

select AVG(x) from (
select a.timestamp - b.timestamp as x 
from table a, table b -- this multiplies a*b ) sub

Answer 3

SELECT AVG(T2.Timestamp - T1.TimeStamp)
  FROM Table T1
  JOIN Table T2 ON T2.ID = T1.ID + 1

Answer 4

In SQL Server, you could write something like that to get that information:

SELECT 
  t1.ID, t2.ID,
  DATEDIFF(MILLISECOND, t2.PriceTime, test2.PriceTime)
FROM table t1
INNER JOIN table t2 ON t2.ID = t1.ID-1
WHERE t1.ID > (SELECT MIN(ID) FROM table)

and if you're only interested in the AVG across all entries, you could use:

SELECT 
  AVG(DATEDIFF(MILLISECOND, t2.PriceTime, test2.PriceTime))
FROM table t1
INNER JOIN table t2 ON t2.ID = t1.ID-1
WHERE t1.ID > (SELECT MIN(ID) FROM table)

Basically, you need to join the table with itself, and use "t1.ID = t2.ID-1" to associate item no. 2 in one table with item no. 1 in the other table and then calculate the time difference between the two. In order to avoid accessing item no. 0 which doesn't exist, use the "T1.ID > (SELECT MIN(ID) FROM table)" clause to start from the second item.

Marc

Answer 5

First thought is a sub-query grabbing the record immediately previous:

SELECT timestamp - 
(select top 1 timestamp from Table T1 where T1.Id < Table.Id order by Id desc)
FROM Table

Then you can take the average of that:

SELECT AVG(delta)
from (SELECT timestamp - 
    (select top 1 timestamp from Table T1 where T1.Id < Table.Id order by Id desc) as delta
    FROM Table) T

There will probably need to be some handling of the null that results for the first row, but I haven't tested to be sure.

Answer 6

try this

   Select Avg(E.Timestamp - B.Timestamp)
   From Table B Join Table E 
      On E.Timestamp = 
           (Select Max(Timestamp)
            From Table
            Where Timestamp < R.Timestamp)