How convert dates to dates range by condition (postgres)

Question

Please help with the request, there is such data: you need to break this data into intervals, in the status column, so that approximately it looks like this:

data_image_example

client| date_start        | date_end          | status
-------------------------------------------------------
1     | 01.06.2019 0:00:00| 01.06.2019 0:02:00| 2
1     | 01.06.2019 0:02:00| 01.06.2019 0:04:00| 1
1     | 01.06.2019 0:04:00| 01.06.2019 0:05:00| 2

Gordon Linoff · Accepted Answer · 2019-06-24 14:22:59Z

1

This is a form of gaps-and-islands with a twist. You can identify the adjacent rows using a difference of row numbers. Then aggregate and use lead() to get the end time:

select client, status, min(time) as starttime,
       lead(min(time)) over (partition by client order by min(time)) as endtime
from  (select t.*,
              row_number() over (partition by client order by time) as seqnum,
              row_number() over (partition by client, status order by time) as seqnum_s
       from t
      ) t
group by (seqnum - seqnum_s), status, client;

answered Jun 24, 2019 at 14:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

ivani Over a year ago

if I have a fixed interval of 1 minute, and suddenly a segment break, how can I take this into mind?

Gordon Linoff Over a year ago

@ivani . . . I would suggest asking a new question with appropriate sample data and desired results. Please add the data as text tables in the question. A db fiddle of some sort is also helpful.

Collectives™ on Stack Overflow

How convert dates to dates range by condition (postgres)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related