StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
3916360
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-10-12T15:33:25.247
FavoriteCount
0
LastActivityDate
2010-10-12T15:33:25.247
LastEditDate
LastEditorUserId
0
OwnerUserId
104223
ParentId
3898589
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
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: <ul> <li>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.</li> <li>Relies solely upon the record id for identification. </li> <li>Does not account for stray logout records or erroneous/duplicates. So it may be less accurate than other methods</li> <li>Requires more convoluted sql than alternative structures</li> </ul> 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. <pre><code>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 </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMySQL Calculate Time Online
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USLeigh
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POMySQL Calculate Time Online
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.