mysql - 我想计算 mysql 中多行的一天 2 个日期之间的时间

Question

我使用此查询来计算用户全天和前 5 天在应用上的登录时间

Select 
  sec_to_time(sum(time_to_sec(TIMEDIFF((IFNULL(logoff_time, ADDTIME(now(), '05:00:00'))),login_time)))) as online_time 
from tb_sessions  
WHERE 
  (login_time BETWEEN DATE(DATE_ADD(now(), INTERVAL (-6) DAY)) 
AND
  ADDTIME(now(), '5:00:00')) AND user_id = 30982
AND TIME(`login_time`) between "00:00:00" AND "23:59:59"
group by DATE(login_time)

现在我有一些新的要求:

计算从 07:00:00 到 23:59:59 的时间

我的表:tb_sessions

id |   user_id   |       login_time      |   logoff_time

1        3098       2017-06-10 06:30:00    2017-06-10 07:45:00
2        3098       2017-06-10 07:45:01    2017-06-10 08:30:00

通过使用上述查询，总在线时间为 = 02:00:00但我只想要 7:00 到 8:30 的时间，所以总时间 = 1:30:00我对案例查询做了一些更改，但没有成功。

您可以通过以下链接查看我的查询:

http://sqlfiddle.com/#!9/4620af/12

Answer 1

您可以使用 greatest 获取同一天 login_time 和 7:00 中最晚的日期，然后再次使用 greatest排除负时差(当注销时间在 7:00 之前时):

Select   date(login_time) date,
         time_format(sec_to_time(sum(greatest(0, time_to_sec(timediff(
             ifnull(logoff_time, now()), 
             greatest(login_time, date_add(date(login_time), interval 7 hour))
         ))))), '%H:%i:%s') online
from     tb_sessions  
where    login_time between date(date_add(now(), interval (-3) day)) and now() 
and      user_id = 3098
and      time(login_time) between "00:00:00" and "23:59:59"
group by date(login_time)

查看它在 sqlfiddle 上运行

说明

内部greatest调用如下所示:

greatest(login_time, date_add(date(login_time), interval 7 hour))

第二个参数仅从 login_time 中获取日期，因此它对应于当天的午夜，然后添加 7 小时:因此这表示当天的 7:00。 greatest 将返回这两个时间戳中最新的一个。如果第一个参数代表的时间大于 7:00，则返回该时间。如果不是，则返回第二个参数(即 7:00)。

外部greatest调用如下所示:

greatest(0, time_to_sec(timediff(....)))

这将确保时差不为负值。以这个记录为例:

   login_time   |   logoff_time
----------------+----------------
2017-06-01 6:30 | 2017-06-01 6:45

在这种情况下，最里面的 greatest 将返回 2017-06-01 7:00，因为 6:30 太早了。但这将使 timediff() 返回一个负时间间隔:-15 分钟。我们真正想要的是 0，因为用户在 7:00 之后没有登录的时间。这就是 greatest 的作用:greatest(0, -15) = 0，因此负值将被消除，并且不会影响总和。

login_time 的条件

我将条件 time(login_time) 留在了“00:00:00”和“23:59:59”之间，但它确实没有做任何事情，因为这对所有人来说都是如此次(除非它们为 null，但这样它们也不会通过第一个条件)。

根据新要求进行编辑

在评论中，您询问当用户不在同一天注销但在 1 或 2 天后保持在线状态时如何按天进行分组。

在这种情况下，您需要一个辅助表来列出您想要在输出中看到的所有日期。例如，这可能是最近 7 天的 7 条记录。

然后您必须将表与它连接起来，以便用户 session 与这样的引用日期至少有重叠。在线时间的计算必须考虑到下线时间可能不会在午夜之前。

这是更新后的查询:

select   ref_date date,
         time_format(sec_to_time(sum(greatest(0, time_to_sec(timediff(
             least(ifnull(logoff_time, now()), date_add(ref_date, interval 1 day ), now()), 
             greatest(login_time,              date_add(ref_date, interval 7 hour))
         ))))), '%H:%i:%s') online
from     (  select date(date_add(now(), interval (-6) DAY)) as ref_date union all 
            select date(date_add(now(), interval (-5) DAY)) union all 
            select date(date_add(now(), interval (-4) DAY)) union all 
            select date(date_add(now(), interval (-3) DAY)) union all 
            select date(date_add(now(), interval (-2) DAY)) union all 
            select date(date_add(now(), interval (-1) DAY)) union all 
            select date(now())
         ) ref
inner join tb_sessions 
        on login_time < date_add(ref_date, interval 1 day)
       and logoff_time > date_add(ref_date, interval 7 hour) 
where    user_id = 3098
group by ref_date

查看它在 sqlfiddle 上运行.