I presume the second output row should be [5,6)...
Yes, sorry. And I suppose the third argument to each of those should be '[)'.
And why are you using a timestamp range when your data are dates?
Didn't want to type our the hours, my my real situation involves timestamptz's.
My first thought is to explode the ranges into distinct dates, order them inside a window, use lag(...) to find breaks,p and assign groups, the for each group take the min and max of the group and form a new range. Not sure exactly what the SQL looks like - especially the range explosion - but should technically work even though performance may suck. Probably want to use lateral and generate_series(...) if you are on a more recent version.
