Calculate time between timestamps for each 2 rows

Question

I have the below table and as you can see in the table, a user has a BREAK OUT and BREAK IN entry for each of the breaks, but i am struggling to calculate the time spent on each break. Can you guys help? Thanks.

ID  Name      Action     Datetime
 2  John Doe  BREAK OUT  2018-05-24 09:00:41
 3  John Doe  BREAK IN   2018-05-24 09:10:45
 4  John Doe  BREAK OUT  2018-05-24 13:00:49
 5  John Doe  BREAK IN   2018-05-24 13:30:52
 6  John Doe  BREAK OUT  2018-05-24 15:30:56
 7  John Doe  BREAK IN   2018-05-24 15:40:59

is there anything you have tried? Where do you wanna calculate it? in mysql query, in php, in js? Where does the data come from? How is the html generated? ...? so many questions. — Jeff, May 25 '18 at 14:02
What about searching/consulting [the manual](http://php.net/manual/en/datetime.diff.php)? — Jeff, May 25 '18 at 14:05
The data comes from the below sql query. I want to calculate it in mysql or php. i am not generating a html...all i want is to get the time difference between each break. Jeff, i know the function, however, i am lacking in the select department....if i could use a select to get the below result, it would be good too: result wanted: BREAK OUT 1/BREAK IN 1 as FIRST BREAK, BREAK OUT 2/BREAK IN 2 as 2ndbreak, BREAK OUT 3/BREAK IN 3 as 3rdbreak — Kriogen Mihalcea, May 25 '18 at 14:05
[This is a pretty straight foreward example in the docs](http://php.net/manual/en/datetime.diff.php#112472) — Jeff, May 25 '18 at 14:07
Possible duplicate of [How to calculate the difference between two dates using PHP?](https://stackoverflow.com/questions/676824/how-to-calculate-the-difference-between-two-dates-using-php) — Jeff, May 25 '18 at 14:09
Jeff, other then telling me to check manual on how to use the DATEDIFF function in sql (Which i already know how to use), do you have any solution? My issue is not that i don't know how to calculate the datediff between 2 dates, it's that i don't know how to calculate for each of the breaks....i would need a side by side result for each break start and break end...which i don't know how to get... — Kriogen Mihalcea, May 25 '18 at 14:12
@KriogenMihalcea Can you please give us your sql request or your php loop where you build your array so I can edit my anwser ? — Mickaël Leger, May 25 '18 at 14:20
I am not using a loop yet since i don't have the correct SQL result. See strawberry's answer below, that is the result i am interested in. I want to get the break times from SQL because i can get the total time between clocking in and clocking out, but had no way of calculating the breaks total time so i can extract it from the total time in a day. — Kriogen Mihalcea, May 29 '18 at 08:42

Mickaël Leger · Answer 1 · 2018-05-25T14:48:24.170

Well, since you don't really say how you get this data and where do you want to display the result, here is just how you can get what you want :

function getDateDiff($breakOut, $breakIn) {
    $dateStart = new DateTime($breakOut);
    $dateEnd   = new DateTime($breakIn); 

    $dateDiff  = $dateStart->diff($dateEnd); 

    return $dateDiff->format("%H:%I:%S");
}

Now you just have to use the function and return the diff :

2   John Doe    BREAK OUT   2018-05-24 09:00:41
3   John Doe    BREAK IN    2018-05-24 09:10:45

var_dump(getDateDiff("2018-05-24 09:00:41", "2018-05-24 09:10:45")); // '00:10:04'

4   John Doe    BREAK OUT   2018-05-24 13:00:49
5   John Doe    BREAK IN    2018-05-24 13:30:52

var_dump(getDateDiff("2018-05-24 13:00:49", "2018-05-24 13:30:52")); // '00:30:03'

6   John Doe    BREAK OUT   2018-05-24 15:30:56
7   John Doe    BREAK IN    2018-05-24 15:40:59

var_dump(getDateDiff("2018-05-24 15:30:56", "2018-05-24 15:40:59")); // '00:10:03'

So if you want to get those result in a PHP loop, maybe you can try this :

// your data array, I assume it looks like this since I have no info
$array(
    2 => array(
        "name" => "John Doe",
        "break" => "2018-05-24 09:00:41",
    ),
    3 => array(
        "name" => "John Doe",
        "break" => "2018-05-24 09:10:45",
    ),
    4 => array(
        "name" => "John Doe",
        "break" => "2018-05-24 13:00:49",
    ),
    5 => array(
        "name" => "John Doe",
        "break" => "2018-05-24 13:30:52",
    ),
    ...
); 

$array_break_time = array();
foreach ($array as $key => $data) {
    if ($key % 2 == 0) {
        if(isset($array[$key + 1])) {
            $array_break_time[] = getDateDiff($data["break"], $array[$key + 1]["break"])
        }
    }
}

The output is :

$array_break_time = array(
    0 => '00:10:04',
    1 => '00:30:03'
    ....
);

But since I have no more details don't know if it's good in your case !

score 0 · Accepted Answer · answered May 25 '18 at 15:03

E.g.:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Action VARCHAR(20) NOT NULL,dt DATETIME PRIMARY KEY);

INSERT INTO my_table VALUES
('BREAK OUT','2018-05-24 09:00:41'),
('BREAK IN','2018-05-24 09:10:45'),
('BREAK OUT','2018-05-24 13:00:49'),
('BREAK IN','2018-05-24 13:30:52'),
('BREAK OUT','2018-05-24 15:30:56'),
('BREAK IN','2018-05-24 15:40:59');

SELECT bi.dt
     , SEC_TO_TIME(TIME_TO_SEC(bi.dt)-TIME_TO_SEC(MAX(bo.dt)))x 
  FROM my_table bi 
  JOIN my_table bo 
    ON bo.dt <= bi.dt 
 WHERE bo.action = 'break out' 
   AND bi.action = 'break in' 
 GROUP 
    BY bi.dt;

+---------------------+----------+
| dt                  | x        |
+---------------------+----------+
| 2018-05-24 09:10:45 | 00:10:04 |
| 2018-05-24 13:30:52 | 00:30:03 |
| 2018-05-24 15:40:59 | 00:10:03 |
+---------------------+----------+

This is great, but how do i add another condition to check based on user_id? I have multiple users in the table with the same action, but the current query only shows one... — Kriogen Mihalcea, May 29 '18 at 08:39
@KriogenMihalcea Your example showed only one user, so I ignored that bit. But you can just amend the select clause, the join clause, and the group by clause accordingly. — Strawberry, May 29 '18 at 09:45

score -1 · Answer 3 · answered May 25 '18 at 14:42

Well if you want to do this in MySQL then you can that using sub-queries like this. 1. Give individual ID's to your employees and store their names in a different table where their employee ID's are a primary key and in that table their personal details are stored. 2. In this table just have employee id, break out and break in time along with the date. then you can use this query and select a particular employee to find out their break time.

select timediff((select breakout_time from table where employee_id = ? and date=?),(select breakin_time from table where employee_id = "same as above" and date="Same as above")) from dual;

Edits or updates are welcomed. Hope this helps.

Calculate time between timestamps for each 2 rows

3 Answers3