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

Calculate duration of time ranges without overlap in PostgreSQL

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

2

Answers


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

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search