Calculate time between one event and the next one in PostgreSQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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'