Calculate time between one event and the next one in PostgreSQL

madeye 0
Viewed 56

I have a table

id user_id created_at status
1 100 2022-12-13 00:12:12 IN_TRANSIT
2 104 2022-12-13 01:12:12 IN_TRANSIT
3 100 2022-12-13 02:12:12 DONE
4 100 2022-12-13 03:12:12 IN_TRANSIT
5 104 2022-12-13 04:12:12 DONE
6 100 2022-12-13 05:12:12 DONE
7 104 2022-12-13 06:12:12 IN_TRANSIT
7 104 2022-12-13 07:12:12 REJECTED

I am trying to calculate the sum for each user of the idle time, so the time between status DONE and next IN_TRANSIT for that user.

The result should be

user_id idle_time
100 01:00:00
104 02:00:00
sql postgresql
3 Answers
select    user_id
         ,idle_time
from     (
          select  user_id
                 ,status
                 ,created_at-lag(created_at) over(partition by user_id order by created_at)   as idle_time
                 ,lag(status) over(partition by user_id order by created_at)                  as pre_status
          from    t 
         ) t
where    status     = 'IN_TRANSIT'
and      pre_status = 'DONE'
user_id idle_time
100 01:00:00
104 02:00:00

Fiddle

DannySlor0

Try the following:

select user_id, min(case status when 'IN_TRANSIT' then created_at end) -
       min(case status when 'DONE' then created_at end) idle_time
from
  (
    select user_id, created_at, status,
           sum(case status when 'DONE' then 1 end) over (partition by user_id order by created_at) as grp
    from table_name 
  ) T
group by user_id, grp
having min(case status when 'IN_TRANSIT' then created_at end) -
       min(case status when 'DONE' then created_at end) is not null

This will find the time difference between 'DONE' status and the first next 'IN_TRANSIT' status, if there is a multiple 'IN_TRANSIT' statuses after and you want to find the difference with the last one just change min(case status when 'IN_TRANSIT' then created_at end) to max.

Also, if there are multiple 'DONE', 'IN_TRANSIT' for a user_id, then they will show as a separate rows in the result, but you can use this query as a subquery to find the sum of all differences grouped by user_id.

See a demo.

userGpjR0

you need to use lag function for finding the diff between first and second row

select user_id,idle_time from (select user_id,status,
 created_at - lag(created_at) over (order by user_id, created_at)
  as idle_time
from calctime)
as drt  WHERE  idle_time > interval '10 sec' and status='IN_TRANSIT'
Sund'er0
Related