SQL: finding longest date gap

sql

Solution

Database-agnostic, something of a variant of richardtallent's, but without the restrictions. (I'm using SQL Server 2008 here, but it shouldn't matter.)

Starting with this setup:

create table test(id int, userid int, time datetime)
insert into test values (1, 1, '2009-03-11 08:00')
insert into test values (2, 1, '2009-03-11 18:00')
insert into test values (3, 1, '2009-03-13 19:00')
insert into test values (4, 1, '2009-03-14 18:00')

Running this query:

select 
  starttime.id as gapid, starttime.time as starttime, endtime.time as endtime, 
  /* Replace next line with your DB's way of calculating the gap */
  DATEDIFF(second, starttime.time, endtime.time) as gap
from 
  test as starttime
inner join test as endtime on 
  (starttime.userid = endtime.userid) 
  and (starttime.time < endtime.time) 
left join test as intermediatetime on 
  (starttime.userid = intermediatetime.userid) 
  and (starttime.time < intermediatetime.time) 
  and (intermediatetime.time < endtime.time) 
where 
  (intermediatetime.id is null)

Gives the following:

gapid  starttime                endtime                  gap
1      2009-03-11 08:00:00.000  2009-03-11 18:00:00.000  36000
2      2009-03-11 18:00:00.000  2009-03-13 19:00:00.000  176400
3      2009-03-13 19:00:00.000  2009-03-14 18:00:00.000  82800

You can then just `ORDER BY` the gap expression descending, and pick the top result.

Some explanation:

- Like richardtallent's answer, you join the table onto itself to find a 'later' record – this basically pairs all records with ANY of their later records, here pairing {1+2, 1+3, 1+4, 2+3, 2+4, 3+4}.

- Then there's another self-join, this time a left join, to find rows in between the two previously selected so {1+2+null, 1+3+2, 1+4+2, 1+4+3, 2+3+null, 2+4+3, 3+4+null}.

- The `WHERE` clause, though, filters these out (keeps only the rows with no intermediate row), hence keeping only {1+2+null, 2+3+null, 3+4+null}. Taa-daa!

If you could, potentially, have the same time in there twice (a 'gap' of 0) then you'll need a way to break ties, as Dems points out. If you can use ID as a tie-breaker, then change e.g.

and (starttime.time < intermediatetime.time)

to

and ((starttime.time < intermediatetime.time) 
  or ((starttime.time = intermediatetime.time) and (starttime.id < intermediatetime.id)))

assuming that 'id' is a valid way to break ties.

In fact, if you know that ID will be monotonically increasing (I know you said 'not sequential,' but it's not clear if this means that they don't increase with each row, or just that the IDs of the two relevant entries may not be sequential because e.g. another user has entries in between), you can use ID instead of time in all the comparisons to make this even simpler.

Problem

I have a table with 2 fields: unique ID, user ID (foreign key) and date-time. This is an access-log to a service. I work in SQL Server but I would appreciate agnostic answers. I would like using SQL to find for a certain user the ID from where the longest gap begins. So for example, say my values are as follows (simplification for one user): ``` ID | User-ID | Time ---------------------------------- 1 | 1 | 11-MAR-09, 8:00am 2 | 1 | 11-MAR-09, 6:00pm 3 | 1 | 13-MAR-09, 7:00pm 4 | 1 | 14-MAR-09, 6:00pm ``` If I search for the longest gap for user 1 I will get ID 2 (it would also be nice to get the length of the gap right there and then, but much less critical). What's the most efficient way to achieve this in SQL? Note: ID is not necessarily sequential. Thank you

Original source