CODEWITHSUNDEEP
sqlpostgresqloverlapdate-rangesql-timestamp
GABRIELA MORENO ROCHA DOS SANT
GABRIELA MORENO ROCHA DOS SANT

Reputation: 1

work thing, need help - how to split ranges that overlapping?

img1 img2

I have 3 tsranges that overlap like the figure below, how can I divide them so that we have 0-1 black, 1-2 green, 2-3 black, 3-4 green and 4-5 black? like img2?

i've tryied to agregate them but cant gat the black time between the greens

id range color
01 2023-05-05T20:00:345Z to 2023-05-05T22:00:345Z BLACK
02 2023-05-05T20:30:345Z to 2023-05-05T20:42:345Z GREEN
03 2023-05-05T21:00:345Z to 2023-05-05T21:20:345Z GREEN

The result should be

id range color
01 2023-05-05T20:00:345Z to 2023-05-05T20:30:345Z BLACK
02 2023-05-05T20:30:345Z to 2023-05-05T20:42:345Z GREEN
03 2023-05-05T20:42:345Z to 2023-05-05T21:00:345Z BLACK
04 2023-05-05T21:00:345Z to 2023-05-05T21:20:345Z GREEN
05 2023-05-05T21:20:345Z to 2023-05-05T22:00:345Z BLACK

Upvotes: -1

Views: 109

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248165

Multiranges are the convenient way to express this.

To get all green ranges, aggregate them:

SELECT range_agg(range) FILTER (WHERE color = 'GREEN')
FROM tab;

Subtract this from the aggregate of the black ranges to get the remainder:

SELECT range_agg(range) FILTER (WHERE color = 'BLACK')
     - range_agg(range) FILTER (WHERE color = 'GREEN')
FROM tab;

You can use the unnest() function to decompose the multiranges into a table of ranges.

Upvotes: 0

Related Questions