总结mysql中的datetimes的多个日期差异

Question

我有一个表，有一个用户和一天的出击(时钟，突破，突破，时钟)。现在假设用户休息2次或更多。我需要汇总所有休息的总时间。我已经创建了一个sqlfiddle，以便更容易地显示我正在尝试做的事情。下面是我的示例：http://sqlfiddle.com/#!2/21542/6现在我需要使用(12:30:21 - 12:04:44) + (12:36:00 - 12:34:00)来获得所有中断的总数。我如何在我的查询中做到这一点。现在假设我在我的表中有10个用户和10天。我需要按天和我知道的用户进行分组。

Answer 1

首先，我会找到某种方法，将打卡记录与同一表中的打卡记录联系起来。然后，我们可以将这些数据放入一个表中，并使用它进行查询。

CREATE TEMPOARY TABLE breakPunchInOut ( 
    SELECT 
        DATE(punchout.PunchDateTime) AS ShiftDate,
        punchout.EmpId, 
        punchout.PunchId AS PunchOutID, 
        (SELECT 
            PunchId 
        FROM
            timeclock
        WHERE
            timeclock.EmpId = punchout.EmpId
        AND
            timeclock.`In-Out` = 1
        AND
            timeclock.PunchDateTime > punchout.PunchDateTime 
        AND
            DATE(timeclock.PunchDateTime) = DATE(punchout.PunchDateTime)
        ORDER BY
            timeclock.PunchDateTime ASC
        LIMIT 1
        ) AS PunchInID
    FROM
        timeclock AS punchout
    WHERE
        punchout.`In-Out` = 0
    HAVING
        PunchInID IS NOT NULL

);

这个查询的工作方式是查找特定日期的所有“打卡”，然后查找同一天发生的下一个“打卡”。HAVING子句过滤出打卡后没有打卡的记录-所以可能是员工当天回家的地方。这一点需要记住，因为如果有人在轮班中途回家，那么他们的休息时间将不会计入总休息时间。

需要指出的是，这种方法只适用于在同一天开始和结束的班次。如果你的夜班从晚上开始，第二天早上结束，那么你必须改变打卡和打卡一起打卡的方式。

现在我们有了这个链接表，使用它为每个员工和每个班次创建摘要报告是相对简单的：

SELECT
    breakPunchInOut.ShiftDate,
    breakPunchInOut.EmpId,
    SUM(
      TIMESTAMPDIFF(MINUTE, punchOut.PunchDateTime, punchIn.PunchDateTime)
    ) AS TotalBreakLengthMins   
FROM
    breakPunchInOut
INNER JOIN
    timeclock AS punchOut
ON
    punchOut.PunchId = breakPunchInOut.PunchOutId
INNER JOIN
    timeclock AS punchIn
ON
    punchIn.PunchId = breakPunchInOut.PunchInId
GROUP BY
    breakPunchInOut.ShiftDate,
    breakPunchInOut.EmpId
;

注意，我们使用的是TIMESTAMPDIFF函数，而不是DATEDIFF函数。DATEDIFF只计算两个日期之间的天数-它不用于时间。