MySQL Calculate Time Online

Question

Would it be possible to calculate time online from a table of something like

logtype   time  
----------------
login     2:30  
logout    2:45  
login     3:20  
logout    4:50  
login     5:00  
login     5:10  
logout    6:00  

It would have extra logins because sometimes the server crashes and doesn't add logout. Can you do something like this in MySQL only or would you have to use possibly ColdFusion? And if so, how would you do it in MySQL/ColdFusion?

All I'm wanting is a (rough) total of time online, hours, days, minutes etc for a particular user.

Answer 1

(See my notes above first, please.)

For the data that you would have, however, by pairing consecutive login/logout pairs, you could process the data to get the information that you need just using a simple dateDiff([datepart], [date 1], [date 2]) function in CF to figure out the difference, if you had already queried the data.

For the data handling, I would suggest adding another row to your table -- one that handles a Session ID. This will ensure proper pairing of your login/logout times. You then could set up your queries something like the following:

<cfquery name="getSession" datasource="[dsn]">
   SELECT DISTINCT sessionID
   FROM logTable
   WHERE userID = <cfqueryparam cfsqltype="cf_sql_integer" value="[inputted userID]" />
</cfquery>

...then, to get the individual login sessions' login times, run a loop like this to output them...

<cfoutput query="getSessions">
   <cfquery name="getSessionInfo" datasource="[dsn]">
      SELECT 
      (SELECT time 
         FROM logTable 
         WHERE sessionID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getSessions.sessionID# />
         AND logType = <cfqueryparam cfsqltype="cf_sql_varchar" value="login" />)
      AS loginTime,
      (SELECT time 
         FROM logTable 
         WHERE sessionID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getSessions.sessionID# />
         AND logType = <cfqueryparam cfsqltype="cf_sql_varchar" value="logout" />)
      AS logoutTime
      FROM logTable
   </cfquery>

   [ancillary output - use your DateDiff here with getSessionInfo.loginTime and getSessionInfo.logoutTime as your two date/times and use the data as you need]
</cfoutput>

My SQL on this may be a little off, but at least it points you in the right direction... Hope this helps... feel free to contact me off-list for more explanation.

Answer 2

In my current project I'm using a bit another definition of the "user activity". For me this is period between session star and last page request. I'm not tracking activity time in exactly this format, but if I needed to -- I would make it this way.

When session starts -- create entry in the table sessions which may look like this:

id (int) auto-incremented PK
sessionid (varchar) -- optional, session id string from ColdFusion
userid (int) FK
session_start (datetime) -- filled when created
last_request (datetime)

Primary key is stored into the user session when entry created.

When user makes request -- update the last_request attribute.

It is also possible to update this when onSessionEnd fired, but you'll need to substract the session timeout from current time to get the last request moment -- which makes this useless.

Additional benefit of this approach is that you can get the aggregated data per user very easily. For example, this will select number of seconds (not tested, may be buggy):

<cfquery datasource="mydsn" name="qGetUserData">
    select userid, sum(unix_timestamp(last_request) - unix_timestamp(session_start)) as timespan
    from sessions 
    group by userid
</cfquery>

Filtering by period is easy too -- simply add where session_start ... clause, same for filtering by specific user(s).

Answer 3

As others have suggested, modifying the table structure is a better approach in the long run. Trying to pair up log records (with no matching key) is difficult, and sometimes impossible. A slightly modified structure would be easier to query, and would yield more accurate results. However, if you absolutely cannot modify the table, it might be possible to calculate a rough total - IF your record ID is numeric/sequential.

The assumption is a "logout" record is always inserted after a "login". So the logout's record ID would always be greater than that of the corresponding "login". A subquery could convert the login information into pairs: ie current and next login record. The corresponding "logout" record id would fall within that range. Once you have matched up login/logouts, use date/time functions to calculate the time elapsed in minutes. Then SUM() the values and format the results however you wish. Note: Obviously proper indexing of the table is a must.

That said, this method only provides an estimate. It has several flaws:

• Some databases do not optimize subqueries very well. So even with indexes, the query performance can be quite poor if there is a lot of data involved.
• Relies solely upon the record id for identification.
• Does not account for stray logout records or erroneous/duplicates. So it may be less accurate than other methods
• Requires more convoluted sql than alternative structures

Anyway, I am not sure of the MySQL syntax off the top of my head. But ignoring the MS Sql Server specific functions, this should be close. The performance in MS SQL was decent with proper indexes, and about 100K records.

SELECT  SUM(DateDiff(n, li.LogTime, lo.LogTime)) AS TotalTimeInMinutes
FROM    (
            SELECT  li.RecordID, li.UserID, li.LogType, li.LogTime,
                   (    SELECT MIN(t.RecordID) FROM  YourTable t
                        WHERE t.UserID   = li.UserID
                        AND   t.LogType  = li.logType
                        AND   t.RecordID > li.RecordID
                    ) AS NextRecordID
            FROM   YourTable li
            WHERE  li.UserID = 123
            AND    li.LogType = 'login'
        ) 
        li INNER JOIN YourTable lo ON lo.UserID = li.UserID
             AND lo.LogType = 'logout'
             AND lo.RecordID BETWEEN li.RecordID AND li.NextRecordID