Determining the length of a user's visit based on SQL timestamps

Question

Hi everyone,

I am divided between using a C# implementation and a pure SQL 2005/2008 implementation. I need to determine the length of usage based on timestamps of their last actions. My table contains "friendID" (uniqueIdentifier) and "lastAction" datetime. Here is an example:

Bob, 1:00 PM 
Bob, 1:01 PM 
Bob, 1:20 PM 
Bob, 1:25 PM 
Jack, 5:00 PM
Bob, 11:20 PM

I want to "time out" a user if they had 60 minutes of inactivity. If your suggestion works correctly, there should be 3 separate sessions from the data above. There is a long break between Bob's session that started at 1 PM and the one that started at 11:20 PM.

Correct results should be: Bob (duration 25 minutes), Jack (duration 0 minutes), Bob (duration 0 minutes)

How would you return those 3 rows using pure SQL or C#?

Thank you very much for your help, it is really needed here.

Answer 1

Record the start time of the first action in the session. Record the last time in the session as well. Look at te Globals.asax events. There should be something like OnSessionEnd (If anyone knows the exact event, please comment). When this happens, record the durration between start time and last time. If you are not using ASP.Net, then there should be equivalent events in WinForms.

Now, the easiest way to deal with the 60 minute timout is to just set it in the web.config. If you don't want to do that, then you will have to have some logic when you are saving your current date to handle this. Like, if the new current date is more than 60 minutes from the old one, then record the old durration and then reset the start and current dates to now.

Answer 2

Here's a pure SQL example:

If you only need the start times of the session:

    select u, t       
    from test a        
    where not exists (
      select 1
      from test b
      where a.u = b.u
      and b.t >= a.t - '60 minutes'::interval
      and b.t 

If you really need the duration of the session:

    select a.u, a.t, c.t
    from test a, test c
    where not exists (
      select 1
      from test b
      where a.u = b.u
      and b.t >= a.t - '60 minutes'::interval
      and b.t  c.t)
    and a.u = c.u
    and a.t 

Answer 3

It sounds like a simple problem, unless I'm misunderstanding the question:

select friendId, max(lastAction) 'lastAction'
  from myTable
  where lastAction >= dateadd(day, -1, getdate())
      -- don't analyze data over 24 hours old
  group by friendId
  having max(lastAction) < dateadd(minute, -60, getdate())

This returns all friends who have had activity in the last 24 hours, but not in the last 60 minutes.