时间戳

首页 > 解决方案 > 如何在postgres中使用移位函数来逐行确定时间差？

r - 如何在postgres中使用移位函数来逐行确定时间差？

问题描述

我希望在 postgresql 中使用某种移位函数来逐行计算时间差。下面我有一张工人表，其中列出了两家公司各自的开始和停止时间。我可以在 R 中很容易地做到这一点。这是我的数据集打印出来和它的 dput：

打印：

         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

输入：

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>)

我使用以下代码和data.table包创建 time_diff 列：

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

我如何在 postgresql 中复制它？

标签： rpostgresqldatetimedifftime

解决方案

LAG在 PostgresQL 中是shift等价的，这是一个很好的教程。

在您的情况下，它应该如下所示：
select age(start_time::timestamp - lag(stop_time,1) over(partition by id):: timestamp as time_diff from my_data

推荐阅读