mysql - 通过计算 TimeDate 记录之间的差异来查找总事件时间

Question

我有一个表，用于为每个user_id使用DateTime注册每分钟的用户日志和其他事件

这是我的表的示例数据

id   |   user_id   |     log_datetime
------------------------------------------
1    |      1      |   2016-09-25 13:01:08 
2    |      1      |   2016-09-25 13:04:08 
3    |      1      |   2016-09-25 13:07:08 
4    |      1      |   2016-09-25 13:10:08 
5    |      2      |   2016-09-25 13:11:08 
6    |      1      |   2016-09-25 13:13:08 
7    |      2      |   2016-09-25 13:13:09 
8    |      2      |   2016-09-25 13:14:10 

我想计算系统上的总活跃时间

<小时/>

更新:预期输出

例如 user_id 1，他的总可用时间应为 00:12:00

由于他的小时和秒是相同的，所以我只需从上一个日志中减去上一个日志，然后从下一个上一个日志中减去上一个日志，依此类推，然后我将对所有减去的值求和这是一个简单的

只是我想在我的范围内循环从最后一条记录到第一条记录的数据

这是一个简单的公式，我希望能让我的问题清楚

SUM((T< n> - T< n-1 >) + (T< n-1 > - T< n-2 >) ... + (T< n-x > - T< n-first > ))

由于user_id 1，他的小时和秒是相同的，所以我将只计算分钟。

(13-10)+(10-7)+(7-4)+(4-1) = 12

user_id   |       total_hours
---------------------------------
   1      |        00:12:00 
   2      |        00:03:02

<小时/>

我写了这段代码

SET @start_date = '2016-09-25';
SET @start_time = '13:00:00';

SET @end_date = '2016-09-25';
SET @end_time = '13:15:00';

SELECT 
`ul1`.`user_id`, SEC_TO_TIME(SUM(TIME_TO_SEC(`dl1`.`log_datetime`))) AS total_hours 
FROM 
`users_logs` AS `ul1`
JOIN `users_logs` AS `ul2` 
    ON `ul1`.`id` = `ul2`.`id`

WHERE  
`ul1`.`log_datetime` >= CONCAT(@start_date, ' ', @start_time)
AND
`ul2`.`log_datetime` <= CONCAT(@end_date, ' ', @end_time)


GROUP BY `ul1`.`user_id`

但是这段代码Sum all Time没有得到差异。这是代码的输出

user_id   |       total_hours
---------------------------------
   1      |        65:35:40 
   2      |        39:38:25

如何计算所有日期时间差异的总和，然后我想每12小时显示他的活跃时间(00:00:00 - 11 :59:59) 和 (12:00:00 - 23:59:59)，并在代码开头选择选定的日期时间段

因此输出将如下所示(只是一个虚拟示例，并非来自给定数据)

user_id   |  total_hours  |   00_12_am  |   12_00_pm  |  
-------------------------------------------------------
   1      |   10:10:40    |   02:05:20  |   08:05:20  |
   2      |   04:10:20    |   01:05:10  |   03:05:30  |

谢谢

Answer 1

因此，您每分钟都会记录一次，如果用户可用，则会有一个日志条目。

然后计算每个用户的日志，这样您就可以得到总分钟数。

select user_id, count(*) as total_minutes
from user_logs
group by user_id;

如果您希望它们显示为时间，请使用 sec_to_time:

select user_id, sec_to_time(count(*) * 60) as total_hours
from user_logs
group by user_id;

关于条件聚合:

select 
  user_id, 
  count(*) as total_minutes,
  count(case when hour(log_datetime) < 12 then 1 end) as total_minutes_am,
  count(case when hour(log_datetime) >= 12 then 1 end) as total_minutes_pm
from user_logs
group by user_id;

更新:为了计算每分钟，只需计算一次不同的分钟，即DATE_FORMAT(log_datetime, '%Y-%m-%d %H:%i').这可以通过 COUNT(DISTINCT ...) 或使用获取不同值的子查询来完成。

完整的查询:

select 
  user_id, 
  count(*) as total_minutes,
  count(case when log_hour < 12 then 1 end) as total_minutes_am,
  count(case when log_hour >= 12 then 1 end) as total_minutes_pm
from 
(
  select distinct 
    user_id, 
    date_format(log_datetime, '%y-%m-%d %h:%i') as log_moment,
    hour(log_datetime) as log_hour
  from.user_logs
) log
group by user_id;