datetime mysql 当天_mysql当日总登录时间

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

I have my session table as

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

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).

Thanks in advance.

EDIT:

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)

解决方案

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;

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`;

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

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