0

How to merge overlapping time intervals if the interval not more than 4 minutes (for example only where id = 1).

I have the next table:

--------------------------------------
id | action    | date
--------------------------------------
1  | started   | 2020-08-18 13:51:02
1  | suspended | 2020-08-18 13:51:04
2  | started   | 2020-08-18 13:52:14
2  | suspended | 2020-08-18 13:52:17
3  | started   | 2020-08-18 13:52:21
3  | suspended | 2020-08-18 13:52:24
1  | started   | 2020-08-18 13:57:21
1  | suspended | 2020-08-18 13:57:22
1  | started   | 2020-08-18 15:07:56
1  | suspended | 2020-08-18 15:08:56
1  | started   | 2020-08-18 15:09:11
1  | suspended | 2020-08-18 15:09:11
1  | started   | 2020-08-18 15:09:11
1  | suspended | 2020-08-18 15:09:13

Expected result:

--------------------------------------
id | action    | date
--------------------------------------
1  | started   | 2020-08-18 13:51:02
1  | suspended | 2020-08-18 13:52:24
1  | started   | 2020-08-18 13:57:21
1  | suspended | 2020-08-18 13:57:22
1  | started   | 2020-08-18 15:07:56
1  | suspended | 2020-08-18 15:09:13

I will be very grateful for your help!

3
  • Your request is not well defined, because there could be a case in which several adjacent rows can form different merging groups of 4-minutes each, depending on where you draw the start and end time stamps. Aug 19, 2020 at 12:21
  • Which version of PostgreSQL do you use? Please always include it with any questions. p.s. welcome to the forum! Aug 19, 2020 at 13:19
  • 1
    If you could, the next time you ask a question, put up a fiddle with your data (like I've done here. It makes life much easier for those trying to help you (less risk of errors and duplication of effort). I did it this time because you're a new contributor, but in future, please provide the DDL and DML as a fiddle! Aug 19, 2020 at 13:58

1 Answer 1

1

As I mentioned in my comment, your request is not well defined because it is unclear how the 4-minute wide time windows are supposed to be chosen. In addition, the nature and importance of the id column is unclear.

Be that as it may, I came up with the following query. It uses window functions in order to define the group edges, and then filters out any row that is not an edge:

WITH t AS (
    SELECT id, action, date
         , min(date) OVER (PARTITION BY round(extract(epoch FROM date)/240)) min_date
         , max(date) OVER (PARTITION BY round(extract(epoch FROM date)/240)) max_date
    FROM tab
)
SELECT id, action, date
FROM t
WHERE date = min_date OR date = max_date;
1
  • 1
    A beautifully elegant solution (+1) - for those who wish to study it, I made up a fiddle available here! I had already input the data anyway, so with your solution, the rest was easy! Aug 19, 2020 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.