1

I have a table with ~three columns, one is for id, and the rest of them contains a timestamp value:

id  |  start_date  |  end_date

I want to make a query that will return me the difference between two events next to each other. So far I wrote sql query:

SELECT start_date, end_date FROM table ORDER BY begin_date asc

and now I don't know how is it possible to get the difference between different rows, for example:

I have three rows:

1  |  1428443952  |  1428444010
2  |  1428443952  |  1428443973
3  |  1428443952  |  1428443975

and I want to make a query that will return me the difference between 

(start_date of id2) - (end_date of id1)
(start_date of id3) - (end_date of id2)
etc.

Is that even possible?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
randomuser2
  • 107
  • 10

1 Answers1

1

Yes, use a join:

select t.start_date - tprev.end_date 
from table t join
     table tprev
     on t.id = tprev.id + 1;

EDIT:

The above is the most efficient method, but it assumes the ids are in order (as suggested by the question). An alternative method is:

select (t.start_date -
        (select tprev.end_date 
         from table tprev
         where tprev.end_date < t.start_date
         order by tprev.end_date
         limit 1
        )
       ) as diff
from table t 
having diff is not null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • what if ids are not in order? for example if the timestamp in the future has id = 1, timestamp now has id = 5 and timestamp in the past has id = 2? Is there a way to sort it before by timestamps instead of ids? – randomuser2 Apr 08 '15 at 22:37
  • Why aren't the IDs in order? When you create the table you can have it auto increment the ID field. See http://stackoverflow.com/a/10283830/4505389 for details. – S. Adam Nissley Apr 09 '15 at 00:44
  • @randomuser2 . . . If the ids are not in order or have gaps, then you cannot use this method. Your question suggests that they are in order, though. – Gordon Linoff Apr 09 '15 at 01:34