Today I needed to find the average session length for our site visitors. We keep a stats log for our site visitors that login. So I know when they logged in, and I know when they clicked the logout button. Not all customers click the logout button (about 55% do), but for our purposes it was good enough to only count those customers who click logout. It is hard to know exactly when their session ended if they don’t click logout.
The trouble I ran into was that I was dealing with data like this:
| 6EBFBED | 2013-04-04 11:45:47 |
| 6EBFBED | 2013-04-04 11:47:29 |
| 3779C2F | 2013-03-27 14:45:04 |
| 3779C2F | 2013-03-27 15:03:30 |
| 5D25F10 | 2012-10-01 14:30:24 |
| 5D25F10 | 2012-10-01 14:33:17 |
The first column is session IDs (I have truncated them from the real session IDs). The second column has 2 dates, representing the login time and the logout time for each session. So how can I take data like that and find the session length for each individual session? Here is one method (there may be better ways to do this):
select jsessionID, sessionLength=dateDiff(second,min(visitDate),max(visitDate))from tempStatsgroup by jsessionIDorder by sessionLength desc
The trick here is that you are using both the SQL Min and Max functions to aggregate the two dates into one row. Then you just have to use the dateDiff function to find the session length. In this case I am getting the session length in seconds. For the above sample records, this is the result:
| 6EBFBED | 102 |
| 3779C2F | 1106 |
| 5D25F10 | 173 |
This code was written for SQL Server 2005…I’m not sure if it will work in other databases, but if not I’m sure something similar will.