mysql当日总登录时间 [英] Current day total login time mysql

问题描述

我正在尝试以小时和分钟为单位计算当天的用户登录时间.

I am trying to calculate user's login time in hours and mins for current day.

我的会话表为

id|  user_id  |  logintime          |logouttime           | isactive
1|  100      |  2017-06-12 22:53:53 |2017-06-13 02:53:53 | 0
2|  100      |  2017-06-13 08:53:53  |2017-06-13 09:13:53 | 0
3|  100      |  2017-06-13 10:53:53 |2017-06-13 11:33:53 | 0
4|  100      |  2017-06-13 11:53:53 |2017-06-13 12:13:53 | 0
5|  100      |  2017-06-13 12:53:53 |NULL (As user is currently logged in)| 1

我想要一个查询，该查询可以计算当天的总登录日，假设今天的日期为13.我还要提到的一件事是，在记录ID 1中，它指出用户确实在12-06-2017登录，但是由于我需要记录时间13，所以它将从2017-06-13 00:00:00开始(如当天的登录时间.

I want a query which can calculate total login day of current day let say the date is 13 today. One more thing i want to mention is that in Record id 1 it states that user did login at 12-06-2017 but as i need record time of 13 so it will start from 2017-06-13 00:00:00 (as login time of that day).

先谢谢了.

到目前为止，我尝试过查询ID 1和ID 5的计算错误. 对于ID 1，它也在计算昨天的分钟数；对于ID 5，它在用户当前登录时给出了空值

So far query i tried which is giving wrong calculation for the id 1 and 5 . for id 1 it is calculating the minutes of yesterday also and for 5th it is giving null as user is currently logged in

SELECT TIMESTAMPDIFF(minute,logintime,logouttime) FROM `table` WHERE user_id= 17 and DATE(logouttime) = DATE(UTC_TIMESTAMP)

推荐答案

对于活动会话，可以使用CURRENT_TIMESTAMP而不是logouttime值.创建带有更正时间戳的视图，以避免在查询中重复相同的select子查询:

For active sessions you could use CURRENT_TIMESTAMP instead of logouttime value. Create view with corrected timestamps to avoid duplicate same select subquery in your query:

CREATE VIEW sessions_view AS
SELECT
    user_id, 
    logintime,
    IF(isactive, CURRENT_TIMESTAMP, logouttime) AS logouttime
FROM sessions_table;

要分割从一天到另一天的会话，请使用带有条件的UNION语法.使用 TIMESTAMPDIFF 函数获取logintime和logouttime值之间的差.要获取作为时间数据类型的聚合持续时间，请使用SUM聚合函数和

To split sessions that passes from one day to another use UNION syntax with conditions. Use TIMESTAMPDIFF function to get difference between logintime and logouttime values. To get aggregated duration that is time data type, use SUM aggregate function and SEC_TO_TIME to convert seconds to DATETIME value:

SELECT
    user_id,
    DATE(logintime) AS `day`,
    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, logintime, logouttime)))
FROM
    (SELECT
        user_id, 
        logintime, 
        IF(DATE(logouttime)>DATE(logintime), TIMESTAMP(DATE(logouttime)), logouttime) AS logouttime
    FROM sessions_view
    UNION ALL
    SELECT
        user_id, 
        TIMESTAMP(DATE(logouttime)) AS logintime, 
        logouttime
    FROM sessions_view
    WHERE DATE(logouttime)>DATE(logintime)) splited_sessions
GROUP BY user_id, `day`;

如果只想获取特定日期的数据，则在查询后附加以下WHERE子句:

If you want to get data for specific date only, then append following WHERE clause to the query:

WHERE DATE(logintime) = '2017-06-13'

这篇关于mysql当日总登录时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！