SQL query to group by day and calculate elapsed time (aggregating rows)

Question

I'm using SQL Server 2008 and looking how to create such a query:

I have a table:

Username nvarchar(50), LogDate datetime

Test1, 2012.01.01 00:00:00
Test2, 2012.01.01 00:00:02
Test1, 2012.01.01 00:00:05
Test3, 2012.01.01 00:00:06
Test1, 2012.01.02 00:01:01
Test2, 2012.01.02 00:02:50
Test1, 2012.01.02 00:01:01

Every few seconds users send updates to insert current datetime with his username in to the log table. I need to calculate how much hours users were "online" every day.

I assume query should sum DateDiff between two rows with the same username grouped by day.

I tried using rank, but haven't got what I want.

Thank you for your help in advance.

"I assume query should sum DateDiff between two rows with the same username grouped by day." Probably not. If I logged on for a couple of minutes just after midnight, then logged on again for a couple of minutes just before the next midnight, that algorithm would show almost 24 hours instead of 4 or 5 minutes. — Mike Sherrill 'Cat Recall', May 07 '12 at 23:51
It is very BAD to insert a row into table. Just insert and record the start time when they login and update end time at regular period. In this way, you know how long they spent on your site. — Jonas T, May 07 '12 at 23:53
Now, I'm not suggesting this is a good way to track users, but I had a question similar to this a while back. I basically broke down the day into minutes and checked for "on time" during that minute. http://stackoverflow.com/questions/761700/how-can-i-check-for-average-concurrent-events-in-a-sql-table-based-on-the-date — scottm, May 07 '12 at 23:58
Jonas, this is GPS updates and I can't have different data stucture. — norcis, May 08 '12 at 06:40
Catcall, you're right. Will tawman's provided code will sum correctly (several minutes, not whole day)? — norcis, May 08 '12 at 06:47

score 1 · Answer 1 · edited May 08 '12 at 20:27

You can extract the date part of the DateTime and group by the UserName to use DateDiff on the MIN / MAX Values:

Table and Data Setup:

create table UserLog (Username nvarchar(50), LogDate DateTime);

insert into UserLog Values('Test1', '2012-01-01 00:00:00');
insert into UserLog Values('Test2', '2012-01-01 00:00:02');
insert into UserLog Values('Test1', '2012-01-01 00:00:05');
insert into UserLog Values('Test3', '2012-01-01 00:00:06');
insert into UserLog Values('Test3', '2012-01-01 00:01:26');
insert into UserLog Values('Test3', '2012-01-01 00:03:22');
insert into UserLog Values('Test3', '2012-01-01 00:05:42');
insert into UserLog Values('Test3', '2012-01-01 00:00:06');
insert into UserLog Values('Test1', '2012-01-02 00:01:01');
insert into UserLog Values('Test2', '2012-01-02 00:02:50');
insert into UserLog Values('Test1', '2012-01-02 00:01:01');

Then you can SELECT as follows:

select UserName, CAST(LogDate AS DATE) as BusinessDay,
MIN(LogDate) as FirstLogEntry, MAX(LogDate) as LastLogEntry,
DATEDIFF(second,MIN(LogDate), MAX(LogDate)) as ElapsedSeconds
FROM UserLog
GROUP BY Username, CAST(LogDate AS DATE)

This will yield the following results and you can calculate hours from the seconds. I showed seconds based on your sample data with test3 user expanded:

UserName BusinessDay FirstLogEntry           LastLogEntry            ElapsedSeconds
-------- ----------- ----------------------- ----------------------- --------------
Test1    2012-01-01  2012-01-01 00:00:00.000 2012-01-01 00:00:05.000 5
Test2    2012-01-01  2012-01-01 00:00:02.000 2012-01-01 00:00:02.000 0
Test3    2012-01-01  2012-01-01 00:00:06.000 2012-01-01 00:05:42.000 336
Test1    2012-01-02  2012-01-02 00:01:01.000 2012-01-02 00:01:01.000 0
Test2    2012-01-02  2012-01-02 00:02:50.000 2012-01-02 00:02:50.000 0

thank tou, tawman, but if user logged on for a couple of minutes just after midnight, then logged on again for a couple of minutes just before the next midnight, that algorithm shows almost 24 hours instead of 4 or 5 minutes. — norcis, May 08 '12 at 06:52

SQL query to group by day and calculate elapsed time (aggregating rows)

1 Answers1