如何计算同一列中时间戳之间的时间差

Question

我有一个oracle数据库，机器人在那里处理索赔。我有机器人id、声明id和进程时间戳。我需要计算机器人从一个索赔到下一个索赔的时间。然后，我需要显示机器人，需要5分钟以上的索赔之间的索赔，并开始索赔的id。举个例子：

Bot ID     Claim ID       Process Timestamp
123        12345          2/19/2018 12:45:26
123        12346          2/19/2018 12:55:11

机器人处理索赔12345花了5分钟多的时间，所以我需要证明：

Bot ID     Claim ID       Minutes
123         12345         9:85

Answer 1

您可以使用lag()的分析形式查看前一行中的机器人ID，其中在window子句中将“previous”定义为基于时间戳。所以：

select bot_id, claim_id,
  process_timestamp - lag(process_timestamp)
    over (partition by bot_id order by process_timestamp) as elapsed
from your_table;

    BOT_ID   CLAIM_ID ELAPSED             
---------- ---------- --------------------
       123      12345                     
       123      12346 +00 00:09:45.000000 

从一个时间戳中减去另一个时间戳得到一个间隔，您无法直接对其进行格式化，但您可以提取其元素并将其作为字符串粘合在一起：

select bot_id, claim_id,
  extract(minute from elapsed) || ':' || extract(second from elapsed) as minutes
from (
  select bot_id, claim_id,
    process_timestamp - lag(process_timestamp)
      over (partition by bot_id order by process_timestamp) as elapsed
  from your_table
)
where elapsed > interval '5' minute;

    BOT_ID   CLAIM_ID MINUTES   
---------- ---------- ----------
       123      12346 9:45      

或者，如果索赔可以超过一个小时：

select bot_id, claim_id,
  (60 * extract(hour from elapsed)) + extract(minute from elapsed)
    || ':' || extract(second from elapsed) as minutes
from (
...

如果列实际上是日期而不是时间戳，则减法将得到一个数字而不是时间间隔。将其转换为字符串可以简单地将一天的小数部分添加到午夜的名义日期中：

select bot_id, claim_id, to_char(date '1900-01-01' + elapsed, 'MI:SS') as minutes
from (
  select bot_id, claim_id,
    process_timestamp - lag(process_timestamp)
      over (partition by bot_id order by process_timestamp) as elapsed
  from your_table
)
where elapsed > 5/1440;

    BOT_ID   CLAIM_ID MINUTES   
---------- ---------- ----------
       123      12346 09:45     

如果它可以超过一个小时，那么您可以将格式模型设置为HH24:MI:SS。(从多个小时中获取分钟数，比如120分钟，要稍微复杂一些。)

过滤器现在是5/1440 -一天有1440分钟，所以这代表5分钟。