时间戳

首页 > 解决方案 > MySQL计算列具有特定值的时间差？

mysql - MySQL计算列具有特定值的时间差？

问题描述

我一直在考虑重新设计我的员工日志表，因为我注意到它在计算时间和节省存储空间方面可能没有那么有效。我说它对空间无效的原因是因为对于一名员工打卡上班、下班、喝茶、喝茶、吃午饭、吃午饭，应用程序会为每个时钟插入一条记录，只有时间和 in_out 状态发生变化. 有效的原因是因为您必须查询六个不同的记录（如果它们存在）才能获得一天的时间。但我正在努力争取最后一点。
我的表如下所示：

logemployeetable    CREATE TABLE `logemployeetable` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) NOT NULL,
  `emp_times` datetime NOT NULL,
  `in_out` enum('IN','OUT','TIn','TOut','LIn','LOut') NOT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `ent_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `index_emplogtb_emp_id` (`emp_id`),
  KEY `index_emplog_ent_id` (`ent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=798 DEFAULT CHARSET=latin1

测试数据集：

emp_id  in_out  emp_times   
1       IN      2019-11-19 05:12:01 
1       OUT     2019-11-19 16:38:04 
1       IN      2019-11-20 05:09:19 
1       OUT     2019-11-20 16:19:47

目前我的总和时间代码如下所示（我目前只是试图总结输入 - 输出时间）：

SELECT  
    l.emp_id, 
    emp.emp_names, 
    emp.emp_number, 
    SUM(TIME_TO_SEC(MAX(l.emp_times))/3600 - TIME_TO_SEC(MIN(l.emp_times))/3600)
FROM 
    employeetable AS emp 
INNER JOIN 
    logemployeetable AS l ON 
        emp.emp_id = l.emp_id 
WHERE DATE(l.emp_dtetime) = '2019-11-20' AND l.in_out  = ‘OUT’ AND l.in_out = ‘IN’ AND  l.emp_id = 1
GROUP BY l.emp_id

这给出了无效的组参数。所以我尝试了这个：

SELECT  
    l.emp_id, 
    emp.emp_names, 
    emp.emp_number,
    TIMESTAMPDIFF(minute, MIN(l.emp_times), MAX(l.emp_times)) AS clock_time
FROM 
    employeetable AS emp 
INNER JOIN 
    logemployeetable AS l ON 
        emp.emp_id = l.emp_id 
WHERE DATE(l.emp_times) BETWEEN '2019-11-19' AND '2019-11-20' AND l.in_out  = 'OUT' AND l.in_out = 'OUT' AND  l.emp_id = 1
GROUP BY l.emp_id

这不计算每天的时间，但实际上计算的加班时间等于 1421 分钟，即 23.6 小时。
有人可以在检查进出时间是否不存在时指出正确的方向来计算每天的时间吗？

标签： mysql

解决方案

我目前只是想总结出入时间

假设每天有一条IN记录和一条记录，一种解决方案是聚合两次。OUT首先按员工和日期，然后按员工。以下查询为您提供每位员工轮班持续时间的总和，以秒为单位。如果对于给定的员工和日期，有一个IN记录但没有OUT（或相反，总和会忽略它。

select
    emp_id,
    sum(shift_duration) total_shift_duration
from (
    select
        emp_id,
        date(emp_times) emp_day,
        unix_timestamp(min(case when in_out = 'OUT' then emp_times end))
            - unix_timestamp(max(case when in_out = 'IN' then emp_times end))
            shift_duration
    from logemployeetable
    group by emp_id, date(emp_times)
) t
group by emp_id

推荐阅读