mysql - 如何在mysql中根据小时拆分登录和注销时间

Question

我有一个存储登录和注销时间的表。我想根据每小时划分总持续时间。表格就像

id | tutor_id | login_time           | logout_time
1  | 12       |'2017-02-20 11:20:20' | '2017-02-20 12:10:00'
2  | 13       |'2017-02-20 11:25:20' | '2017-02-20 12:20:00'
3  | 12       |'2017-02-20 13:20:20' | '2017-02-20 13:50:00'
4  | 13       |'2017-02-20 12:30:20' | '2017-02-20 12:50:00'
5  | 13       |'2017-02-20 13:10:20' | '2017-02-20 14:20:00'

我想为每个 tutor_id 在那个小时内以分钟为单位登录持续时间。

输出应该是这样的

tutor_id| hour | Duration(mins)
12      | 11   | 40
12      | 12   | 10
12      | 13   | 30
13      | 11   | 35
13      | 12   | 30
13      | 13   | 50
13      | 12   | 20

Answer 1

您可以尝试以下方法。

我创建了一个如下所示的临时表，

create table schedule_temp as 
select id, tutor_id, login, logout, 
       case 
        when Extract(hour from logout) > Extract(hour from login) 
          then 1 
        else 0 
       end isUnionRequired 
from schedule_table

然后使用该临时表，我使用下面的查询来获得预期的结果

select tutor_id, hour, sum(minute_value) Duration 
from(
    select tutor_id, login, Extract(hour from login) hour, 60-Extract(minute from login) minute_value
     from schedule_temp
     where isUnionRequired = 1
    union all
    select tutor_id, login, Extract(hour from login), Extract(minute from logout) - Extract(minute from login)
     from schedule_temp
     where isUnionRequired = 0
    union all 
    select tutor_id, logout, Extract(hour from logout), Extract(minute from logout)
     from schedule_temp
     where isUnionRequired = 1) t1
group by tutor_id, hour;

上面的查询给了我下面的结果

我得到了以下结果，

您可以简单地将整个流程放入存储过程中。