Calculate total hours per day based on "TIME_IN" data in one row and "TIME_OUT" data on the next row

Question

I need to calculate the actual time spent in the office per day. I have seen several examples online, but they all have TIME_IN and TIME_OUT in the same row. My application puts entries into a new row each time an "enter" or "exit" is recorded by the tracking device. I am able to calculate overall time per day for a "normal" day with the following query:

use timelog;

mysql> SELECT TIMEDIFF(MAX(date_time), MIN(date_time)) FROM timelog WHERE day_of_month="11";

+------------------------------------------+
| TIMEDIFF(MAX(date_time), MIN(date_time)) |
+------------------------------------------+
| 09:06:00                                 |
+------------------------------------------+
1 row in set (0.00 sec)

The data for all IN/OUT on the 11th:

mysql> SELECT status,date_time FROM timelog WHERE day_of_month="11";

+--------+---------------------+
| status | date_time           |
+--------+---------------------+
| IN     | 2017-04-11 08:39:00 |
| OUT    | 2017-04-11 09:42:00 |
| IN     | 2017-04-11 10:03:00 |
| OUT    | 2017-04-11 10:38:00 |
| IN     | 2017-04-11 10:43:00 |
| OUT    | 2017-04-11 12:09:00 |
| IN     | 2017-04-11 13:20:00 |
| OUT    | 2017-04-11 13:24:00 |
| IN     | 2017-04-11 13:26:00 |
| OUT    | 2017-04-11 14:06:00 |
| IN     | 2017-04-11 14:13:00 |
| OUT    | 2017-04-11 17:45:00 |
+--------+---------------------+

Table: timelog

Description: mysql> desc timelog;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| status       | char(3)  | NO   |     | NULL    |       |
| date_time    | datetime | YES  |     | NULL    |       |
| day_of_week  | char(9)  | YES  |     | NULL    |       |
| month        | char(10) | YES  |     | NULL    |       |
| day_of_month | int(2)   | YES  |     | NULL    |       |
| year         | int(4)   | NO   |     | NULL    |       |
| hour         | int(2)   | YES  |     | NULL    |       |
| minute       | int(2)   | YES  |     | NULL    |       |
| time_of_day  | time     | YES  |     | NULL    |       |
| SSID         | char(15) | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+

All Data for the 11th:

mysql> SELECT * FROM timelog WHERE day_of_month="11";
+--------+---------------------+-------------+-------+--------------+------+------+--------+-------------+----------------+
| status | date_time           | day_of_week | month | day_of_month | year | hour | minute | time_of_day | SSID           |
+--------+---------------------+-------------+-------+--------------+------+------+--------+-------------+----------------+
 |IN     | 2017-04-11 08:39:00 | Tuesday     | April |           11 | 2017 |    8 |     39 | 08:39:00    | CGP Financial
 |OUT    | 2017-04-11 09:42:00 | Tuesday     | April |           11 | 2017 |    9 |     42 | 09:42:00    | CGP Financial
 |IN     | 2017-04-11 10:03:00 | Tuesday     | April |           11 | 2017 |   10 |      3 | 10:03:00    | CGP Financial
 |OUT    | 2017-04-11 10:38:00 | Tuesday     | April |           11 | 2017 |   10 |     38 | 10:38:00    | CGP Financial
 |IN     | 2017-04-11 10:43:00 | Tuesday     | April |           11 | 2017 |   10 |     43 | 10:43:00    | CGP Financial
 |OUT    | 2017-04-11 12:09:00 | Tuesday     | April |           11 | 2017 |   12 |      9 | 12:09:00    | CGP Financial
 |IN     | 2017-04-11 13:20:00 | Tuesday     | April |           11 | 2017 |   13 |     20 | 13:20:00    | CGP Financial
 |OUT    | 2017-04-11 13:24:00 | Tuesday     | April |           11 | 2017 |   13 |     24 | 13:24:00    | CGP Financial
 |IN     | 2017-04-11 13:26:00 | Tuesday     | April |           11 | 2017 |   13 |     26 | 13:26:00    | CGP Financial
 |OUT    | 2017-04-11 14:06:00 | Tuesday     | April |           11 | 2017 |   14 |      6 | 14:06:00    | CGP Financial
 |IN     | 2017-04-11 14:13:00 | Tuesday     | April |           11 | 2017 |   14 |     13 | 14:13:00    | CGP Financial
 |OUT    | 2017-04-11 17:45:00 | Tuesday     | April |           11 | 2017 |   17 |     45 | 17:45:00    | CGP Financial
+--------+---------------------+-------------+-------+--------------+------+------+--------+-------------+----------------+

Now for my question(s)... :-)

How can I best calculate actual time spent in the office based on the time between each IN and OUT? I'd like to be able to mark the time between say "09:42" and "10:03" as time not worked, and the time between "10:03" and "10:38" as worked, and so on. I have tried negating all "IN" times except for the first of the day, and adding, but that didn't seem to work.

Can someone help me craft a SQL statement (I am by no means even a novice) that will step through each day's IN/OUT timestamps and calculate "actual" time spent in the office?

Since I am asking for the moon, I might as well ask for Mars too. As I am subject to on-call, it is not uncommon for me to work well past midnight, or as in the case of this morning, be called int the office from 01:00 to 02:45, so I'd like to be able to factor "past midnight" and "came in early, went home, came back in at 08:00" into the equation as well.

If you need more information, or clarification on anything I have posted, please let me know. Your help is very much appreciated.

Bryan

Whatt's the desired behavior for IN before midnight, OUT after? Also - is there ever a chance that someone would work have an "IN" on day n, with its associated out being on day n+2? — RDFozz, Apr 13, 2017 at 15:15
If I came in at 08:00 on Monday and was in the office until 04:00 Tuesday, I'd like to be able to attribute 08:00 -00:00 to Monday, and 00:00 - 04:00 to Tuesday (with the possibility of coming back in Tuesday and working additional hours). It is possible to come IN on day N and work until day N+2, but that would be very rare indeed. — Bryan Moorehead, Apr 13, 2017 at 15:33
An example from an extended day. I manually added 23:50 and 00:00 |IN | 2017-03-30 08:52:00 | Thursday | March | 30 | 2017 | 8 | 52 | 08:52:00 |OUT | 2017-03-30 23:59:00 | Thursday | March | 30 | 2017 | 23 | 59 | 23:59:00 |IN | 2017-03-31 00:00:00 | Friday | March | 31 | 2017 | 0 | 0 | 00:00:00 |OUT | 2017-03-31 01:45:00 | Friday | March | 31 | 2017 | 1 | 45 | 01:45:00 |IN | 2017-03-31 10:29:00 | Friday | March | 31 | 2017 | 10 | 29 | 10:29:00 — Bryan Moorehead, Apr 13, 2017 at 15:50
I haven't figured out how to fix the formatting yet. Apologies. I'll keep on RTFM... — Bryan Moorehead, Apr 13, 2017 at 15:53
It's better to add it to the question (where formatting options are less limited) anyway - not everyone reads the comments, and past a certain point they get hidden. — RDFozz, Apr 13, 2017 at 16:14

RMathis · Accepted Answer · 2017-04-13 22:06:37Z

It seems the most important thing is matching time out with the time in. The query below shows one way this can be accomplished.

The approach below attributes time worked to when the individual "clocked" in... there is no attempt to attribute the time to the day in which it occurred (there are other questions on this site that address that need).

 select in_time,
         ifnull(out_time,now()) as out_time,
         unix_timestamp(ifnull(out_time,now())) - unix_timestamp(in_time) as seconds_in
    from (select date_time in_time,
                 (select min(date_time)
                    from timelog
                   where status='OUT'
                     and date_time > t.date_time) as out_time
            from timelog t
           where status='IN'
         ) as in_out;
+---------------------+---------------------+------------+
| in_time             | out_time            | seconds_in |
+---------------------+---------------------+------------+
| 2017-04-11 08:39:00 | 2017-04-11 09:42:00 |       3780 |
| 2017-04-11 10:03:00 | 2017-04-11 10:38:00 |       2100 |
| 2017-04-11 10:43:00 | 2017-04-11 12:09:00 |       5160 |
| 2017-04-11 13:20:00 | 2017-04-11 13:24:00 |        240 |
| 2017-04-11 13:26:00 | 2017-04-11 14:06:00 |       2400 |
| 2017-04-11 14:13:00 | 2017-04-11 17:45:00 |      12720 |
+---------------------+---------------------+------------+
6 rows in set (0.00 sec)

Now to sum the seconds in for each day...

select date(in_time),
       sum(seconds_in)
 from (select in_time,
              ifnull(out_time,now()) as out_time,
              unix_timestamp(ifnull(out_time,now())) - unix_timestamp(in_time) as seconds_in
         from (select date_time in_time,
                      (select min(date_time)
                         from timelog
                        where status='OUT'
                          and date_time > t.date_time) as out_time
                 from timelog t
                where status='IN'
              ) as in_out
      ) as q1
group by date(in_time);
+---------------+-----------------+
| date(in_time) | sum(seconds_in) |
+---------------+-----------------+
| 2017-04-11    |           26400 |
+---------------+-----------------+
1 row in set (0.01 sec)

Thank you very much. What would be the best method to convert the seconds per day to hours and minutes? — Bryan Moorehead, Apr 14, 2017 at 14:22
I answered my own question! I modified: sum(seconds_in) to : TIME_FORMAT(SEC_TO_TIME(sum(seconds_in)),'%Hh %im') — Bryan Moorehead, Apr 14, 2017 at 14:44
One last question. I modified the SQL statement slightly to pull the last 30 days, and print Column Headers: mysql> \. calculate_hours_per_day_for_past_30_days.sql +------------+--------------+ | Date | Hours Worked | +------------+--------------+ | 2017-03-28 | 00h 34m | | 2017-03-29 | 09h 03m | | 2017-03-30 | 11h 39m | | 2017-03-31 | 08h 31m | I renamed the SSID field to details | varchar(100) | I have tried to alter the SQL to include details, ex. "Had to work late to migrate code", but think a temp table may be needed. Help again is appreciated. — Bryan Moorehead, Apr 26, 2017 at 14:35
mysql> SELECT day_of_week, month, day_of_month, details FROM timelog where details IS NOT NULL; +-------------+-------+--------------+------------------------------------------------------+ | day_of_week | month | day_of_month | details | Friday | April | 21 | Both Dave and Chad left early, so I had to stay late | 1 row in set (0.00 sec) — Bryan Moorehead, Apr 26, 2017 at 14:36

Kevin Martin · Accepted Answer · 2019-03-12 22:31:28Z

I too am having this same problem. In the past with different tools, I have solved this by sorting the employees, then sorting the date time descending. At this point, I created an index column starting from 0 to add to my query(A). I then made a duplicate query(B) after that and created an index column starting with 1. I joined query A&B using the Index columns I made so that their Out Times had previous records in the same row. I then did an If statement saying if the record has an out status, take the difference between the time stamp and the previous record time stamp. This is a complex problem, because sometimes people can leave the building without scanning their fingerprints (Yeah we have those) if someone holds the door open or something, so you can have two IN records back to back and Vice Versa with the out records. You also need to account for those situations, but all in all that method works pretty well and you can use Power BI for the indexing. I was just hoping someone could solve this problem on the MYSQL side. Here is what Power BI gave me.

Stack Exchange Network

Calculate total hours per day based on "TIME_IN" data in one row and "TIME_OUT" data on the next row

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
datetime2
or ask your own question.

Hot Network Questions

Calculate total hours per day based on "TIME_IN" data in one row and "TIME_OUT" data on the next row

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqldatetime2 or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
datetime2
or ask your own question.