Finding gap between date range Postgres

Question

I have this table and need to find gaps between intervals

Records may be overlapping.

user |    start_time            |     end_time
user1|2018-09-26T02:16:52.023453|2018-09-26T03:12:04.404477
user1|2018-09-25T22:15:49.593296|2018-09-26T00:15:52.016497
user1|2018-09-25T20:13:02.358192|2018-09-25T22:15:49.593296

Expected output will be

 user |    start_time            |     end_time
 user1|2018-09-26T00:15:52.016497|2018-09-26T02:16:52.023453

Paule

What do you mean gap between date? could you explain more detail about your logic?' – Monometallism

the gap between consecutive date range (which the start_time, end-time) @Monometallism – Paule

Check window functions LEAD and LAG - postgresql.org/docs/9.6/static/functions-window.html – Nimitz

Answer 1

demo: db<>fiddle

You can use the lag window function (https://www.postgresql.org/docs/current/static/tutorial-window.html). This function moves a value from a previous row into the current. Now it is possible to compare the moves end_time with the current start_time and check for the gap.

SELECT
    "user",
    prev_end_time as gap_start_time,
    start_time as gap_end_time
FROM (
    SELECT 
        *, 
        lag(end_time) OVER (PARTITION BY "user" ORDER BY start_time) as prev_end_time
    FROM time_intervals 
    ORDER BY start_time
) s
WHERE start_time > prev_end_time

Result

user    gap_start_time               gap_end_time
user1   2018-09-26 00:15:52.016497   2018-09-26 02:16:52.023453

---

Notice that "user" is a reserved word in Postgres. You should better use another column name.

Meany

so moving the end date to the next row and comparing that basically to the start date of that row? – Paule

That's what it does :) – Meany

cool, sorry another question, what exactly does the PARTITION do? – Paule

I gave a link to the window function tutorial. You should read it. The window function partitions the table. It's a kind of grouping. If you have 2 users the function would to the "row shift" only within each user group separately because it would not make any sense to search gaps over two users, wouldn't it? If the user name doesn't matter you would only need the ORDER BY clause. Example: dbfiddle.uk/… – Meany

make sense, it's like a GROUP BY. – Paule

Yes. But with the benefit that you don't have to aggregate all columns (as shown in the tutorial) – Meany