How to return the difference between timestamps in SQL?

Question

I run a query in my table, which causes it to return timestamps in ascending order (from oldest to newest). Like in I, put the line ORDER BY timestamp .

I need my results to have a “Days Taken” column that contains the difference between each timestamp, i.e. (Timestamp 2 - Timestamp 1), (Timestamp 3 - Timestamp 2), (Timestamp 4 - Timestamp 3) and so on. How to do it 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 cannot use the value column as an index because it is defined as a string, not an int, so this> does not work for me. Days Taken is the value that I would like to calculate.

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

Answer 1

Try something in this direction:

 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)