w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MySQL Query For Total Online Time Based On Login/Logout Entries

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





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.