Question 114592 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate time between one event and the next one in PostgreSQL

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
Tags:

3

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search