I have the following postgres database table:

There are different actions at different timestamps. But here I only care about the actions START und END. For each START/END pair I want to have one row in my result table like so:

There is never an overlap between START-END intervals. So there is never two STARTs and then two ENDs for example. Can anyone point me in the right direction how to wirte a query for that? I did not provide any SQL because I do not really know where to start and what language features to use. Thanks!

CodePudding user response：

You need a self join with a twist: join all rows with action = START to the corresponding row with action = END where the timestamp is the smallest value that is bigger than the one with action = START.

This can be achieved using a lateral join:

select st."timestamp" as "start timestamp",
       en."timestamp" as "end timestamp"
from the_table st
  cross join lateral (
     select min(t2."timestamp") as "timestamp"
     from the_table t2
     where action = 'END'
      and t2."timestamp" > st."timestamp"
  ) en 
where st.action = 'START'
      

The cross join isn't really a cross join as the sub-query will never return more than one row.