MYSQL 如何获取两行数据之间的时间差？

Question

我有一个充当某种时钟的数据库。每次员工超时或超时，都会创建一个新的时间戳行，记录该员工、他们所在的工作、是否超时以及时间。

只要他们在 12 小时内不进出超过一次，我就可以正常工作，但是我如何改进我的代码，以便他们可以在一天内进出多个作业？

SELECT
    CONCAT(employees.Fname, ' ', employees.Lname) AS name, 
    timeInData.jobPK, 
    timeInData.timestamp AS TimeIn, 
    timeOutData.timestamp AS TimeOut, 
    timeInData.TSPK,
    timeOutData.TSPK, 
    TIMEDIFF(timeOutData.timestamp, timeInData.timestamp) AS hours,
    ROUND((sec_to_time(TIME_TO_SEC(timeOutData.timestamp) - TIME_TO_SEC(timeInData.timestamp)) /10000 * employees.cost), 2) AS empcost
FROM
    (SELECT * FROM timestamps WHERE status = 1 AND jobPK=$job) AS timeInData
    LEFT JOIN (SELECT * FROM timestamps WHERE status = 0 AND jobPK=$job) AS timeOutData ON timeInData.jobPk = timeOutData.jobPk AND timeInData.employeePK = timeOutData.employeePK
    JOIN employees ON timeInData.employeePK = employees.employeePK
WHERE
    timeOutData.timestamp BETWEEN timeInData.timestamp AND date_add(timeInData.timestamp, INTERVAL 720 MINUTE)
ORDER BY
    name ASC, TimeIn ASC;

现在，如果一天中有 4 个时间戳，它将获取每个时间并减去每个超时，从而导致数据行过多。对此有什么解决办法吗？或者甚至只是一些可以帮助回答我的问题的其他文章。我在互联网上搜索过，但没有运气。

谢谢

Answer 1

如果你想要一个纯 SQL 解决方案，我会尝试以下几行:

SELECT
    e.*,
    ti.timestamp AS TimeIn,
    to.timestamp AS TimeOut
FROM employees AS e
JOIN timestamps AS to
    ON to.employeePK = e.employeePK
    AND to.jobPK = $job
    AND to.status = 0
JOIN timestamps AS ti
    ON ti.employeePK = e.employeePK
    AND ti.jobPK = $job
    AND ti.timestamp = (
        SELECT MAX(ts.timestamp)
        FROM timestamps AS ts
        WHERE ts.employeePK = e.employeePK
        AND ts.jobPK = $job
        AND ts.status = 1
    )
    AND ti.status = 1

但在我看来，处理这样的时间序列并不真正属于关系代数。我宁愿使用存储过程来创建游标(迭代员工)和临时表(以包含运行总和)。或者甚至把所有的计算都拿出来用同样的思路编程语言。有了员工PK，就可以轻松选择过去 12 小时内的所有条目并在编程语言方面对其进行处理。