How to use a shifting function in postgres to determine time difference by row?

Question

I am looking to use some sort of shift function in postgresql to figure out time difference by row. Below i have a table of workers with start and stop times for each of two companies. I can do this very easily in R. Here is my data set print out and the dput for it :

print:

         id          start_time           stop_time   company   time_diff
 1: worker_1 2017-10-20 22:06:23 2017-10-20 22:09:48 company 1          NA
 2: worker_1 2017-10-20 22:28:45 2017-10-20 23:16:56 company 2  0.31583333
 3: worker_1 2017-10-20 22:37:07 2017-10-20 22:43:14 company 1 -0.66361111
 4: worker_1 2017-10-20 22:59:30 2017-10-20 23:05:52 company 1  0.27111111
 5: worker_1 2017-10-20 23:39:57 2017-10-20 23:43:00 company 1  0.56805556
 6: worker_1 2017-10-20 23:53:54 2017-10-21 00:07:27 company 1  0.18166667
 7: worker_1 2017-10-20 23:53:54 2017-10-21 00:07:27 company 1 -0.22583333
 8: worker_2 2017-10-19 18:03:43 2017-10-19 18:04:48 company 1          NA
 9: worker_2 2017-10-19 18:04:48 2017-10-19 18:05:08 company 1  0.00000000
10: worker_2 2017-10-19 18:05:51 2017-10-19 18:18:47 company 1  0.01194444
11: worker_2 2017-10-19 18:50:39 2017-10-19 21:01:14 company 1  0.53111111

dput:

structure(list(id = c("worker_1", "worker_1", "worker_1", "worker_1", 
"worker_1", "worker_1", "worker_1", "worker_2", "worker_2", "worker_2", 
"worker_2"), start_time = structure(c(1508551583, 1508552925, 
1508553427, 1508554770, 1508557197, 1508558034, 1508558034, 1508450623, 
1508450688, 1508450751, 1508453439), class = c("POSIXct", "POSIXt"
)), stop_time = structure(c(1508551788, 1508555816, 1508553794, 
1508555152, 1508557380, 1508558847, 1508558847, 1508450688, 1508450708, 
1508451527, 1508461274), class = c("POSIXct", "POSIXt")), company = c("company 1", 
"company 2", "company 1", "company 1", "company 1", "company 1", 
"company 1", "company 1", "company 1", "company 1", "company 1"
), time_diff = c(NA, 0.315833333333333, -0.663611111111111, 0.271111111111111, 
0.568055555555556, 0.181666666666667, -0.225833333333333, NA, 
0, 0.0119444444444444, 0.531111111111111)), row.names = c(NA, 
-11L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000000007a81ef0>)

I create the time_diff column using the following code with data.table package:

my_data[,time_diff:=as.numeric(
  difftime(start_time
           ,shift(stop_time, type = "lag")
           , units = "hours")
),.(id)]

How do i replicate this in postgresql?

Answer 1

LAG is the shift equivalent in PostgresQL, here is a nice tutorial.

In your case it should look like:
select age(start_time::timestamp - lag(stop_time,1) over(partition by id):: timestamp as time_diff from my_data