Finding date intersections

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Finding date intersections
Date: 2014-10-24 02:10:55
Message-ID: CACfv+pKE3srkaSbaWnFO4tBRhDixRb0c=BktdJBmzBMQpEmTeg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table of sales that have possibly overlapping time ranges. I want
to find all the timeranges where there's an active sale. How would you do
that?

create table sales (
times tstzrange
);

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')

Thanks,
Joe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message blackbee045 2014-10-24 05:45:00 Re: Emulating flexible regex replace
Previous Message John Smith 2014-10-23 22:30:11 is there a warm standby sync trigger?