mySql计算日期差异总和

Question

我正在开发一个员工登录系统，其中用户检入和检出时间是记录器。我有以下mySql表模式，我想从其中查询某个员工在特定月份的总工作时间。

AttendanceId    UserId  Operation   CreatedDate
24                 4       1       2016-03-20 23:18:59
25                 4       2       2016-03-20 23:19:50
26                 4       1       2016-03-20 23:20:28
27                 4       2       2016-03-20 23:20:31

操作1用于检入，操作2用于检出。有人能帮我构建这个查询吗？

Answer 1

一个令人愉快的复杂问题，谢谢。我的查询处理以下内容：

未精确计算小时数的

• 出席率。秒数的总和除以calculation.
• Attendances末尾的3600 (感谢当月已经开始(有一个操作为“1”的条目)但尚未完成(没有对应的操作“2”)的strawberry)
• Attendances )。

我使用以下数据进行测试：

INSERT INTO Attendance(UserId, Operation, CreatedDate) VALUES
(4, 1, '2016-01-01 15:00:00'),
(4, 2, '2016-01-01 19:00:00'),
(4, 1, '2016-01-31 23:00:00'),
(4, 2, '2016-02-01 01:00:00'),
(4, 1, '2016-02-20 23:18:59'),
(4, 2, '2016-02-20 23:19:50'),
(4, 1, '2016-02-20 23:20:28'),
(4, 2, '2016-02-20 23:20:31'),
(4, 1, '2016-02-29 23:00:00'),
(4, 2, '2016-03-01 01:00:00'),
(4, 1, '2016-03-02 15:00:00'),
(4, 2, '2016-03-02 18:00:00'),
(4, 1, '2016-03-22 10:00:00');

该查询选择特定月份的所有用户小时数。在一个查询中选择一个月以上的结果更加复杂，因为出席率可能跨越月份界限，如果需要，最简单的方法可能是迭代月份并重复运行查询，适当地调整SQL中的四个日期。

最里面的查询选择所有用户的所有到达时间和对应的出发时间。然后，外部查询将它们限制为当前月份，计算两次之间的差值，并按用户对它们求和。

SELECT UserId, SUM(TIMESTAMPDIFF(
    SECOND,
    GREATEST(TimeIn, '2016-02-01'),
    LEAST(COALESCE(TimeOut, NOW()), '2016-03-01'))) / 3600 HoursInMonth
FROM (SELECT TimeIn.UserId, TimeIn.CreatedDate TimeIn, MIN(TimeOut.CreatedDate) TimeOut
      FROM Attendance TimeIn
      LEFT JOIN Attendance TimeOut ON TimeOut.UserId = TimeIn.UserId
                                  AND TimeOut.Operation = 2
                                  AND TimeOut.CreatedDate > TimeIn.CreatedDate
      WHERE TimeIn.operation = 1
      GROUP BY TimeIn.AttendanceId
      ORDER BY TimeIn.CreatedDate) TimeInOut
WHERE DATE_FORMAT(TimeIn,  '%Y-%m') = '2016-02'
   OR DATE_FORMAT(TimeOut, '%Y-%m') = '2016-02'
   OR (DATE_FORMAT(TimeIn, '%Y-%m') < '2016-02' AND TimeOut IS NULL)
GROUP BY UserId;