SQL greenhorn alarm:
I have a table with timestamp information (44 rows) and I now would like to calculate and extract the delta between any two consecutive rows. How can I do this? I select my data with a query like:

SELECT * from "table" where "RecognitionTimestamp" >= '2018-10-03 00:00:00' and "RecognitionTimestamp" <= '2018-10-04 9:00:00'

I'm using pgAdmin4 to submit the query and look at the data.


You can use Windows Functions which provides calculation across a set of table rows that are somehow related to the current row. The list of functions are in General-Purpose Window Functions section of the documentation and The lead() and lag() functions are answer of your question. Your query will should like this;

  "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
  lead("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") - "RecognitionTimestamp" AS delta_next
FROM "table" 
  "RecognitionTimestamp" >= '2018-10-03 00:00:00' AND 
  "RecognitionTimestamp" <= '2018-10-04 9:00:00'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.