Select longest common timerange

Question

Hi,

I have a table where I have datetimes associated with an ID:

┌────────────────┬──────────────────────┐
│ location_id    | datetime             |
├────────────────┼──────────────────────┤
│ 200333         | 2008-01-01 00:00:00  |
│ 200333         | 2008-01-01 01:00:00  |
│ 200333         | 2008-01-01 02:00:00  |
| ...            | ...                  |
│ 200333         | 2009-10-23 21:00:00  |
│ 200333         | 2009-10-23 22:00:00  |
│ 200333         | 2008-10-23 23:00:00  |
│ 200768         | 2008-06-01 00:00:00  |
│ 200768         | 2008-06-01 01:00:00  |
│ 200768         | 2008-06-01 02:00:00  |
| ...            | ...                  |
│ 200768         | 2009-12-31 00:00:00  |
│ 200768         | 2009-12-31 00:00:00  |
│ 200768         | 2009-12-31 00:00:00  |
└────────────────┴──────────────────────┘

What would be the way to select the longest time period these two overlapping location_id's share? In this case, the desired output would be:

┌──────────────────────┬──────────────────────┐
│ start                | end                  |
├──────────────────────┼──────────────────────┤
│ 2008-06-01 00:00:00  | 2009-10-23 23:00:00  |
└──────────────────────┴──────────────────────┘

I can easily get the longest period available using MIN() and MAX() but how would I go about selecting the maximum of minimum datetimes and minimum of maximum datetimes?

Oh, and this table contains 19 000 000 rows, so bonus points for suggestions that run fast :)

Answer 1

You can try something

SELECT  MAX(MinDates) MaximumMinDate,
        MIN(MaxDates) MinimumMaxDate
FROM    (
            SELECT  location_ID,
                    MIN([datetime]) MinDates,
                    MAX([datetime]) MaxDates
            FROM    Table
            WHERE   location_ID IN (200333, 200768)
            GROUP BY location_ID
        ) sub

And then just replace the ids with what you need.

Answer 2

I hope this works out for you:

SELECT l1.maxtime, l2.mintime FROM 
(SELECT location_id, min(datetime), max(datetime)
FROM table
GROUP BY location_id
) as l1(id, mintime, maxtime)
,
(SELECT location_id, min(datetime), max(datetime)
FROM table
GROUP BY location_id
) as l2(id, mintime, maxtime)
WHERE 
l1.id <> l2.id
HAVING max(l1.maxtime-l2.mintime);