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

What do you mean gap between date? could you explain more detail about your logic?' — D-Shih
– D-Shih, Commented Oct 1, 2018 at 7:48
the gap between consecutive date range (which the start_time, end-time) @D-Shih — ellaRT
– ellaRT, Commented Oct 1, 2018 at 7:52
Check window functions LEAD and LAG - postgresql.org/docs/9.6/static/functions-window.html — Pavel Horal
– Pavel Horal, Commented Oct 1, 2018 at 7:59

S-Man · Accepted Answer · 2018-10-01 08:09:59Z

8

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.

edited Oct 1, 2018 at 8:09

answered Oct 1, 2018 at 8:00

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ellaRT Over a year ago

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

S-Man Over a year ago

That's what it does :)

ellaRT Over a year ago

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

S-Man Over a year ago

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/…

S-Man Over a year ago

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

|

Collectives™ on Stack Overflow

Finding gap between date range Postgres

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related