On Fri, Oct 24, 2014 at 11:02 AM, David G Johnston <david.g.johnston@xxxxxxxxx> wrote:
John McKown wrote
>> insert into sales values
>> (tstzrange('2014-1-1', '2014-1-2')),
>> (tstzrange('2014-1-2', '2014-1-3')),
>> (tstzrange('2014-1-2', '2014-1-4')),
>> (tstzrange('2014-1-5', '2014-1-6'));
>>
>> -- want back:
>> -- tstzrange('2014-1-1', '2014-1-4')
>> -- tstzrange('2014-1-6', '2014-1-6')
>>
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.
Thanks! I'll look into this.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Finding-date-intersections-tp5824102p5824194.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
