How to return difference between timestamps in SQL?

Question

I run a query on my table that makes it return timestamps in ascending order (oldest to newest). As in I put in a line ORDER BY timestamp.

I need my results to have a column called "Days Taken" which contains the difference between each of the timestamps, i.e. (Timestamp 2 - Timestamp 1), (Timestamp 3 - Timestamp 2), (Timestamp 4 - Timestamp 3) and so on. How do I do this using SQL?

value   timestamp             Days Taken
 2      2016-03-16 05:11:40    -
 3      2016-03-18 03:46:42    ?
 4      2016-03-18 04:09:44    ?
 5      2016-03-21 04:01:46    ?
 6      2016-03-22 04:38:17    ?

I'm unable to use the column value as an index because it is defined as a string and not an int which is why this doesn't work for me. Days Taken is the value I'd like to calculate.

Edited to add: I'm running DbVisualizer for Vertica which does not seem to support subqueries in the ON clause.

Answer 1

Try something along these lines:

select datediff(dd, a.timestamp, b.timestamp)
  from #Table   a
  join #Table   b  on a.timeStamp = (select max(c.timeStamp) 
    from #Table c where c.timeStamp < b.timeStamp)