Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to find most overlapping time period with date ranges

Suppose you have a table with an identifier, a start time and an end time. These start and end times can be any length of time. The start time is always before the end time. Assume there are no nulls.

What kind of query would tell me the most "popular" time, i.e. where the two ranges in each row overlaps with the most other rows?

The real life application of this is that it's a table recording users' sign in and sign out times. I want to write a query that will tell me when the most concurrent users were logged in and see what period of time this was.

Thank you.

like image 635
Oliver Martin Avatar asked Jul 14 '14 16:07

Oliver Martin


1 Answers

There are several approaches to this. One uses correlated subqueries. That isn't much fun. Instead, let's use the cumulative sum method because you have Oracle.

The key is to start with a list of timestamps with a value of +1 for a start and -1 for an end. This is easy:

select t.*
from ((select starttime as thetime, 1 as value from table t) union all
      (select endtime, -1 as value from table t)
     ) t

Now, the cumulative sum of the value tells you the number of active overlaps at any given time:

select t.*, sum(value) over (order by thetime) as numactives
from ((select starttime as thetime, 1 as value from table t) union all
      (select endtime, -1 as value from table t)
     ) t

This solves your problem. You probably want to add an order by numactives desc for the specific times.

like image 181
Gordon Linoff Avatar answered Nov 15 '22 06:11

Gordon Linoff