Detect overlapping date/time records and show which records it is overlapping with using SQL

Question

The challenge here is to use SQL to detect overlapping bids using date/time and reveal which bids it is overlapping with.

Consider the table and records:

-------------------------------------------------------------------
| person_id | bid_id  | start_at            | end_at              |
-------------------------------------------------------------------
| 1         | 10      | 2016-10-30 09:00 AM | 2016-10-30 11:00 AM |
| 1         | 20      | 2016-10-30 10:00 AM | 2016-10-30 12:00 PM |
| 1         | 30      | 2016-10-30 05:00 PM | 2016-10-30 06:00 PM |
| 1         | 40      | 2016-10-30 11:45 AM | 2016-10-30 02:00 PM |
| 2         | 50      | 2016-10-30 07:00 PM | 2016-10-30 08:00 PM |
| 2         | 60      | 2016-10-30 07:00 PM | 2016-10-30 08:00 PM |
| 2         | 70      | 2016-10-30 09:00 PM | 2016-10-30 10:00 PM |
-------------------------------------------------------------------

Desired output:

--------------------------------------------
| person_id | bid_id | overlapping_bid_ids |
--------------------------------------------
| 1         | 10     | { 20 }              |
| 1         | 20     | { 10, 40 }          | 
| 1         | 40     | { 20 }              |
| 2         | 50     | { 60 }              |
| 2         | 60     | { 50 }              |
--------------------------------------------

Answer 1

You can use array_agg() and a join:

select b.person_id, b.bid_id, array_agg(b2.bid_id)
from bids b join
     bids b2
     on b2.person_id = b.person_id and
        b2.end_at > b.start_at and
        b2.start_at < b.end_at and 
        b2.bid_id <> b.bid_id 
group by b.person_id, b.bid_id;

Note the logic for two time periods overlapping. They overlap if the first starts before the second ends and the first ends after the first starts. This accounts for partial overlaps as well as full overlaps. You can also use Postgres's implementation of the ANSI standard range operator overlap if you prefer.