Alex Rivera | Logout

SQL Datediff - find datediff between rows

Asked 2011-04-20T10:04:16.273
26

I would like to query a database using sql to show the difference in time between id 1,2,3 and so on. basically it will compare the row below it for all records. any help would be appreciated. 

IDCODE  DATE TIME        DIFFERENCE (MINS)
1      02/03/2011 08:00        0
2      02/03/2011 08:10        10
3      02/03/2011 08:23        13
4       02/03/2011 08:25        2
5       02/03/2011 09:25        60
6       02/03/2011 10:20        55
7       02/03/2011 10:34        14

Thanks!

sql sql-server t-sql datediff
Edit
Report

1 Answer

21

Standard ANSI SQL solution. Should work in PostgreSQL, Oracle, DB2 and Teradata:

SELECT idcode, 
       date_time, 
       date_time - lag(date_time) over (order by date_time) as difference
FROM your_table
answered 2011-04-20T10:06:57.783

Your Answer