CODEWITHSUNDEEP
postgresqlgaps-and-islands
ellaRT
ellaRT

Reputation: 1366

Finding gap between date range Postgres

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

Upvotes: 5

Views: 2627

Answers (1)

S-Man
S-Man

Reputation: 23666

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.

Upvotes: 8

Related Questions