Geek Answers Handbook

MySQL Query for Complete I / O Based Online Time

Okay, so I need to execute a bit of a nasty MySQL query, and I can't think about how to do this in my life.

I have the following content:

id date name join_or_leave 1 yyyy-mm-dd hh-mm-ss user1 join 2 yyyy-mm-dd hh-mm-ss user2 join 3 yyyy-mm-dd hh-mm-ss user1 leave 4 yyyy-mm-dd hh-mm-ss user3 join 5 yyyy-mm-dd hh-mm-ss user2 leave 6 yyyy-mm-dd hh-mm-ss user1 join 7 yyyy-mm-dd hh-mm-ss user4 join 8 yyyy-mm-dd hh-mm-ss user1 leave

In this way, it logs all login / logout times for users.

What I have to calculate is the total time spent on the Internet (the total number for all users, but I can work out this amount after receiving the total amount for each user, if the total number of all users is very slow).

Hope this is obvious what I need to do here, it is obvious that all dates go down (so that regardless of the time you add the join / exit record); but how would I do it, I just don’t know .: P

+7
sql mysql datetime logging sum
source share
2 answers

should do the trick:

 SELECT player_name, TIME_FORMAT(SEC_TO_TIME( IF(SUM(TIME) < 0, SUM(TIME) + TO_SECONDS(NOW()), IF(SUM(TIME) > 63000000000, SUM(TIME) - TO_SECONDS(NOW()), SUM(TIME)) ) ),'%Hh %im') AS TOTAL_TIME FROM ( SELECT TO_SECONDS(c.date) * - 1 AS TIME, c.player_name FROM player_playtime c WHERE join_or_leave = 'join' UNION SELECT TO_SECONDS(date) AS TIME, player_name FROM player_playtime WHERE join_or_leave = 'leave' ) t GROUP BY player_name WITH ROLLUP ;

script: http://sqlfiddle.com/#!2/ebe7a1/4

+4
source share

Add a column for the session time for each user that you update when the user logs off. Then we are only talking about summarizing the application in this column. Of course, it will only show you the time for users who are logged out, ignoring the fact that they are logged in again.

0
source share

More articles:


All Articles