Calculate the time difference between two rows for work time

Question

I have one table:

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

I need output to look like this:

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

I'm unable to join the rows so any help will be appreciated There is a third col that represent the worker tag but this should be easy to group.

Does the table have *any* way to identify which check-in matches which check-out? Perhaps some ID of the person/item that checked in or out? Without that, the calculated times will be wrong since the rows will not be associated with the corresponding check-in/out. — Wrokar, Aug 03 '18 at 16:49
@Wrokar I believe it's the date. I also believe OP has a typo in their Desired Results/Output where that last record should be fore `2018-01-03` (perhaps clearing some of the confusion?) — JNevill, Aug 03 '18 at 16:57
Are you missing a `2018-01-01 12:30:40 none missing checkout`? — Schwern, Aug 03 '18 at 17:54
Sorry, the last requested result has a typo and should be 2018-01-03 as the input data. I just fix it. — Sam, Aug 03 '18 at 22:12

score 1 · Answer 1 · answered Aug 03 '18 at 18:38

There's probably a better way to do this, but here's how you can do this as a three part query.

First, find all the checkins and use a sub-query to select the first checkout after that checkin on the same day.

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 |
+------------+----------+----------+

Note: I assume you're missing the 12:30:40 row from your set.

Then in a separate query, find all the check outs that have no checkin. This is done with a self-join against the checkin rows.

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 them together.

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 |
+------------+----------+----------+

The final part is the formatting and ordering. Rather than try to do this in this already giant query, make a new query just for the formatting. Use this query as a subquery table.

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  |
+------------+----------+----------+------------------+

Or save yourself some hassle and do the formatting on the receiving end.

Schwern, Thanks. Your query work great but how can I add the working time between the check-in to check-out. Is there a way to speed the query as it's taking a long time to fetch just 22 rows (almost 20 sec). — Sam, Aug 03 '18 at 23:13
Adding the working time result also with some errors like Truncated incorrect time value: '55568' ErrorNr. 1292 — Sam, Aug 03 '18 at 23:31
@Sam The time between checkin and checkout is already there. Use the usual ways to check for performance problems, but I'm going to guess having `date(timedate)` in the where clause will kill performance because it cannot be indexed. See [this question for work arounds](https://stackoverflow.com/questions/95183/how-does-one-create-an-index-on-the-date-part-of-datetime-field-in-mysql). Amir's answer is much cleaner, even if you have to expand out all the views manually. — Schwern, Aug 04 '18 at 00:12
Changing time(checkout - checkin) to TIMEDIFF(checkout,checkin) fix the Truncated issue. Now just need to speed this query — Sam, Aug 04 '18 at 00:14

Amir Pashazadeh · Accepted Answer · 2018-08-04T14:16:57.043

Using views simplifies the queries, so use something like

create view in_gate_times as
select date(date_time) xdate, time(date_time) xtime
  from gate_times where gate_op = 'check-in';

create view out_gate_times as
select date(date_time) xdate, time(date_time) xtime
  from gate_times where gate_op = 'check-out';

and then use following query

select i.xdate, i.xtime, ifnull(o.xtime, 'missing')
  from in_gate_times i
  left join out_gate_times o on i.xdate = o.xdate
 union
select o.xdate, ifnull(i.xtime, 'missing'), o.xtime
  from in_gate_times i
 right join out_gate_times o on i.xdate = o.xdate
 order by 1, 2, 3

Check it on SQLFiddle

If using union made your query slow, use union all with following change

select i.xdate, i.xtime, ifnull(o.xtime, 'missing')
  from in_gate_times i
  left join out_gate_times o on i.xdate = o.xdate
 union all
select o.xdate, ifnull(i.xtime, 'missing'), o.xtime
  from in_gate_times i
 right join out_gate_times o on i.xdate = o.xdate
 where i.xdate is null
 order by 1, 2, 3

check this on SQLFiddle

If views are forbidden just replace each view with its query, so the last query will be

select i.xdate, i.xtime, ifnull(o.xtime, 'missing')
  from (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-in') i
  left join (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-out') o
    on i.xdate = o.xdate

union all

select o.xdate, ifnull(i.xtime, 'missing'), o.xtime
  from (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-in') i
 right join (select date(date_time) xdate, time(date_time) xtime from gate_times where gate_op = 'check-out') o
    on i.xdate = o.xdate
 where i.xdate is null
 order by 1, 2, 3

It can be checked on SQLFiddle

Amir, thanks but I couldn't check this as I can't use view just SQL query. — Sam, Aug 03 '18 at 23:19
@Sam You can manually replace `in_gate_times` and `out_gate_times` with the selects in the views. This approach is more elegant than my solution. — Schwern, Aug 04 '18 at 00:15
Amir, WOW. Your query fetch the info 100 time faster. Instead 20 Sec it's 0.2 Sec. THANK YOU. — Sam, Aug 04 '18 at 13:17
@Sam You're welcome. If views are forbidden, you can replace each view usage in the last query with its query (in the parentheses). And if the answer is proper for you, just accept it. — Amir Pashazadeh, Aug 04 '18 at 13:52

Calculate the time difference between two rows for work time

2 Answers2

Linked