5

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
ellaRT
  • 1,346
  • 2
  • 16
  • 39

1 Answers1

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.

S-Man
  • 22,521
  • 7
  • 40
  • 63
  • so moving the end date to the next row and comparing that basically to the start date of that row? – ellaRT Oct 01 '18 at 08:07
  • That's what it does :) – S-Man Oct 01 '18 at 08:08
  • cool, sorry another question, what exactly does the `PARTITION` do? – ellaRT Oct 01 '18 at 08:13
  • 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: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=90b11211f083992e52f2694ee305a506 – S-Man Oct 01 '18 at 08:20
  • make sense, it's like a GROUP BY. – ellaRT Oct 01 '18 at 08:24
  • 1
    Yes. But with the benefit that you don't have to aggregate all columns (as shown in the tutorial) – S-Man Oct 01 '18 at 08:26