Calculate duration of time ranges without overlap in PostgreSQL-CodePudding

I'm on Postgres 13 and have a table like this

| key | from             | to 
-------------------------------------------
| A   | 2022-11-27T08:00 | 2022-11-27T09:00
| B   | 2022-11-27T09:00 | 2022-11-27T10:00
| C   | 2022-11-27T08:30 | 2022-11-27T10:30

I want to calculate the duration of each record, but without overlaps. So the desired result would be

| key | from             | to               | duration
----------------------------------------------------------
| A   | 2022-11-27T08:00 | 2022-11-27T09:00 | '1 hour'
| B   | 2022-11-27T09:00 | 2022-11-27T09:45 | '45 minutes' 
| C   | 2022-11-27T08:30 | 2022-11-27T10:00 | '15 minutes'

I guess, I need a subquery and subtract the overlap somehow, but how would I factor in multiple overlaps? In the example above C overlaps A and B, so I must subtract 30 minutes from A and then 45 minute from B... But I'm stuck here:

SELECT key, (("to" - "from")::interval - s.overlap) as duration
FROM time_entries, (
  SELECT (???) as overlap
) s

CodePudding user response：

select 
   key,
   fromDT,
   toDT,
   (toDT-fromDT)::interval -
   COALESCE((SELECT SUM(LEAST(te2.toDT,te1.toDT)-GREATEST(te2.fromDT,te1.fromDT))::interval  
    FROM time_entries te2 
    WHERE (te2.fromDT<te1.toDT or te2.toDT>te1.fromDT)
      AND te2.key<te1.key),'0 minutes')  as duration
from time_entries te1;

output:

key	fromdt	todt	duration
A	2022-11-27 08:00:00	2022-11-27 09:00:00	01:00:00
B	2022-11-27 09:00:00	2022-11-27 10:00:00	01:00:00
C	2022-11-27 08:30:00	2022-11-27 10:30:00	00:30:00

I renamed the columns from and to to fromDT and toDT to avoid using reserved words.
a, step by step, explanation is in the DBFIDDLE

CodePudding user response：

Another approach.

WITH DATA AS
    (SELECT KEY,
            FROMDT,
            TODT,
            MIN(FROMDT) OVER(PARTITION BY FROMDT::DATE
                                                                                ORDER BY KEY) AS START_DATE,
            MAX(TODT) OVER(PARTITION BY FROMDT::DATE
                                                                        ORDER BY KEY) AS END_DATE
        FROM TIME_ENTRIES
        ORDER BY KEY) ,STAGING_DATA AS
    (SELECT KEY,
            FROMDT,
            TODT,
            COALESCE(LAG(START_DATE) OVER (PARTITION BY FROMDT::DATE
                                                                                                                                        ORDER BY KEY),FROMDT) AS T1_DATE,
            COALESCE(LAG(END_DATE) OVER (PARTITION BY FROMDT::DATE
                                                                                                                                ORDER BY KEY),TODT) AS T2_DATE
        FROM DATA)
SELECT KEY,
    FROMDT,
    TODT,
    CASE
                    WHEN FROMDT = T1_DATE
                                        AND TODT = T2_DATE THEN (TODT - FROMDT) ::Interval
                    WHEN T2_DATE < TODT THEN (TODT - T2_DATE)::Interval
                    ELSE (T2_DATE - TODT)::interval
    END
FROM STAGING_DATA;