Mysql – Calculate total hours per day based on “TIME_IN” data in one row and “TIME_OUT” data on the next row

datetime2MySQL

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

Best Answer

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)
Related Question