MySQL Query For Total Online Time Based On Login/Logout Entries
Ok, so I have to perform a bit of a nasty MySQL query, and I can't for the life of me think of how to do it.
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
So it logs all login/logout times for users.
What I have to calculate is the total time spent online (the total for all users, but I can work out this total after getting the total for each user if getting the total of all users is going to be very slow).
Hopefully it's obvious what I need to do here, obviously all of the dates are going downwards (so whatever time the join/leave entry was added); but how I'd go about doing it I just don't know. :p
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
;
fiddle: http://sqlfiddle.com/#!2/ebe7a1/4
Add a column for the session time for each user, which you update when the user logs out. Then it is only a question of summing app that column. Of course, it will only show you the time for users that have logged out, desregarding the fact that they might have logged in again.
链接地址: http://www.djcxy.com/p/17008.html上一篇: cv :: imshow有时非常慢