时间戳

首页 > 解决方案 > 计算工作时间的两行之间的时间差

mysql - 计算工作时间的两行之间的时间差

问题描述

我有一张桌子：

timedate                 workclock
2018-01-01 09:00:00      check-in
2018-01-01 12:30:40      check-in
2018-01-02 09:00:00      check-in
2018-01-02 11:29:00      check-out
2018-01-03 14:29:00      check-out

我需要输出看起来像这样：

date          checkin      checkout     working-time
2018-01-01    09:00        none         missing checkout
2018-01-02    09:00        11:29        02:29
2018-01-03    none         14:29        missing checkin

我无法加入行，因此将不胜感激有第三个 col 代表工人标签，但这应该很容易分组。

标签： mysql

解决方案

可能有更好的方法来执行此操作，但这里是您可以通过三部分查询来执行此操作的方法。

首先，找到所有签到并使用子查询选择当天签到之后的第一个签出。

select
    date(t1.timedate) as date,
    time(t1.timedate) as checkin,
    (
        select time(t2.timedate)
        from timetracker t2
        where workclock = 'check-out'
          and date(t2.timedate) = date(t1.timedate)
          and t1.timedate < t2.timedate
     ) as checkout
from timetracker t1
where t1.workclock = 'check-in'

+------------+----------+----------+
| date       | checkin  | checkout |
+------------+----------+----------+
| 2018-01-01 | 09:00:00 | NULL     |
| 2018-01-01 | 12:30:40 | NULL     |
| 2018-01-02 | 09:00:00 | 11:29:00 |
+------------+----------+----------+

注意：我假设您缺少集合中的 12:30:40 行。

然后在一个单独的查询中，找到所有没有签入的签出。这是通过针对签入行的自联接来完成的。

select
    date(t3.timedate) as date,
    null as checkin,
    time(t3.timedate) as checkout
from timetracker t3
left join timetracker t4
     on date(t3.timedate) = date(t4.timedate) and
        t4.workclock = 'check-in'
where t3.workclock = 'check-out'
  and t4.timedate is null

+------------+---------+----------+
| date       | checkin | checkout |
+------------+---------+----------+
| 2018-01-03 |    NULL | 14:29:00 |
+------------+---------+----------+

union他们在一起。

select
    date(t1.timedate) as date,
    time(t1.timedate) as checkin,
    (
        select time(t2.timedate)
        from timetracker t2
        where workclock = 'check-out'
          and date(t2.timedate) = date(t1.timedate)
          and t1.timedate < t2.timedate
        order by t2.timedate
        limit 1
     ) as checkout
from timetracker t1
where t1.workclock = 'check-in'

union

select
    date(t3.timedate) as date,
    null as checkin,
    time(t3.timedate) as checkout
from timetracker t3
left join timetracker t4
     on date(t3.timedate) = date(t4.timedate) and
        t4.workclock = 'check-in'
where t3.workclock = 'check-out'
    and t4.timedate is null

+------------+----------+----------+
| date       | checkin  | checkout |
+------------+----------+----------+
| 2018-01-01 | 09:00:00 | NULL     |
| 2018-01-01 | 12:30:40 | NULL     |
| 2018-01-02 | 09:00:00 | 11:29:00 |
| 2018-01-03 | NULL     | 14:29:00 |
+------------+----------+----------+

最后一部分是格式化和排序。与其尝试在这个已经很庞大的查询中执行此操作，不如为格式化创建一个新查询。将此查询用作子查询表。

select
    t.date,
    case
    when t.checkin is null then
        'none'
    else
        t.checkin
    end as "checkin",
    case
    when t.checkout is null then
        'none'
    else
        t.checkout
    end as "checkout",
    case
    when checkout is null then
        'missing checkout'
    when checkin is null then
        'missing checkin'
    else
        time(checkout - checkin)
    end as "working-time"
from (
    select ...
    union
    select ...
) t
order by t.date, t.checkin

+------------+----------+----------+------------------+
| date       | checkin  | checkout | working-time     |
+------------+----------+----------+------------------+
| 2018-01-01 | 09:00:00 | none     | missing checkout |
| 2018-01-01 | 12:30:40 | none     | missing checkout |
| 2018-01-02 | 09:00:00 | 11:29:00 | 02:29:00         |
| 2018-01-03 | none     | 14:29:00 | missing checkin  |
+------------+----------+----------+------------------+

或者省点麻烦，在接收端进行格式化。

推荐阅读