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

Postgresql – Find total time spent with overlapping and repeat times

I have a table where the same ID can have multiple entries with start time and end time. How would I calculate the total time spent by the ID. The result I would want for the table below would be 11 minutes.

ID start time end time
123 5:30:00 5:35:00
123 5:36:00 5:40:00
123 5:36:00 5:40:00
123 5:36:00 5:39:00
123 5:38:00 5:42:00

I tried a few things but I was only able to take care of the cases where the start times were the same, not overlapping.

Tags:

3

Answers


  1. 0

    Assuming that the start and stop times are of type timestamp with time zone and the table is called times, you could use multirange magic and date arithmetic to find the answer:

    SELECT m.id, sum(upper(r.r) - lower(r.r)) AS duration
FROM (SELECT id,
             range_agg(tstzrange(start_time, end_time)) AS mr
      FROM times
      GROUP BY id) AS m
   CROSS JOIN LATERAL unnest(m.mr) AS r(r)
GROUP BY m.id;
    Login or Signup to reply.
  2. 0

    Use LAG() Over() analytic function to handle overlapings and calculate total difference in seconds (transform it later to hours,minutes,seconds if needed):

    CREATE TABLE TBL 
(
    ID  INT,
    START_TIME  TIME,
    END_TIME    TIME
);
--   S a m p l e    D a t a :
INSERT INTO TBL (ID, START_TIME, END_TIME) VALUES ('123', '5:30:00', '5:35:00');
INSERT INTO TBL (ID, START_TIME, END_TIME) VALUES ('123', '5:36:00', '5:40:00');
INSERT INTO TBL (ID, START_TIME, END_TIME) VALUES ('123', '5:36:00', '5:40:00');
INSERT INTO TBL (ID, START_TIME, END_TIME) VALUES ('123', '5:38:00', '5:42:00');

    --    S Q L :
SELECT  t.ID,
        SUM( ( EXTRACT(Hour From t.END_TIME) * 60 * 60 + 
               EXTRACT(Minute From t.END_TIME) * 60 + 
               EXTRACT(SEcond From t.END_TIME) ) -
             ( EXTRACT(Hour From t.START_PERIOD) * 60 * 60 + 
               EXTRACT(Minute From t.START_PERIOD) * 60 + 
               EXTRACT(SEcond From t.START_PERIOD) ) 
          ) as DIFF_SECONDS
FROM ( SELECT t.ID, t.START_TIME, t.END_TIME, 
              Case When t.START_TIME >= Coalesce(LAG(t.END_TIME) Over(Partition By ID Order By t.START_TIME), t.START_TIME)
                   Then t.START_TIME
              Else Coalesce(LAG(t.END_TIME) Over(Partition By ID Order By t.START_TIME), t.START_TIME)
              End as START_PERIOD
       FROM TBL t ) t
GROUP BY  t.ID

    /*  R e s u l t :
ID   DIFF_SECONDS
---  ------------
123           660
    Login or Signup to reply.
  3. 0

    Using the instruction from https://dba.stackexchange.com/questions/270952/how-to-get-non-overlapping-distinct-intervals-from-a-postgresql-table

    I applied your example (Exposing id, replacing f_time with StartTime, and replacing l_time with EndTime)

    SELECT id, sum(t3.max_end - t3.min_start) as TotalTime
FROM
(
SELECT 
  id,
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(StartTime) OVER (PARTITION BY s ORDER BY StartTime, EndTime desc) AS min_start, 
  MAX(EndTime) OVER (PARTITION BY s ORDER BY StartTime desc, EndTime) AS max_end,
  s
FROM
(
  SELECT   *,
  SUM(ovl) OVER (ORDER BY t1.StartTime ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
  FROM
  (
  SELECT id, StartTime, EndTime,
  CASE
    WHEN LAG(EndTime) OVER () > StartTime THEN 0
    ELSE 1
  END AS ovl
  FROM Example
  ) AS t1
  ORDER BY id, EndTime
)AS t2
ORDER BY s, rn ASC, min_start  
) t3
WHERE t3.rn = 1
GROUP BY id;

    fiddle

    Non-overlapping distinct intervals:

    Interval No. Interval start Interval stop Duration
    1 05:30:00 05:35:00 00:05:00
    2 05:36:00 05:42:00 00:06:00

    Total:

    id totaltime
    123 00:11:00
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search