[FIXED] Calculating time difference with values retrieved from MySQL TIME field

Issue

I've done a bit of searching and there is a bunch of information on calculating time difference between two times using strtotime('09:00:00) and then putting this against another value.

Currently, I have a database that stores regular hours for staff like so:

+----------+-----------+------------+-------------+--------------+
| staff_id |    day    | start_time | finish_time | total_breaks |
+----------+-----------+------------+-------------+--------------+
|        1 | Monday    | 18:00:00   | 22:00:00    |            0 |
|        2 | Wednesday | 09:00:00   | 17:30:00    |           30 |
+----------+-----------+------------+-------------+--------------+

start_time and finish_time are stored as TIME values in MySQL and total_breaks as an INT which I should probably convert to TIME values as well and represent as 00:30:00 or 00:00:00 for consistency.

What I want to be able to do is display a table that displays all of this information, as well as their total hours worked and then total hours minus any breaks.

I've been able to generate a table with the staff_id, day, start_time and finish_time but am struggling to figure out the best way to calculate the total amount of time worked etc.

I'm using Laravel, and have read that strtotime('00:00:00') is a great way to do this but this won't work as I'm not using a string but rather pulling the time from the database.

What is the best way to calculate the time difference between the two times, and then take in to account the breaks as well.

Have I even set this up correctly? I don't want to set the times up as a DATETIME as I'm just wanting to calculate the time difference of their normal working hours which they do from week to week - although I can always use dummy dates to get this working if necessary.

Any ideas?

Solution

No need to make things overly complicated.

You can always convert your database times to epoch time using strtotime

<?php
$start_time = '2015-09-23 22:00:00'; // pulled from DB
$finish_time = '2015-09-24 06:00:00'; // pulled from DB
$starttime = strtotime($start_time); // convert to timestring
$endtime = strtotime($finish_time); // convert to timestring
$diff = $endtime - $starttime; // do the math

$total_breaks = 30; // pulled from DB
$breaks = $total_breaks*60; // minutes * seconds per minute
$hours = ($diff - $breaks)/60/60; // do the math converting seconds to hours
?>

The above code will output like this...

<?php
echo 'clocked in: '.$start_time.'<br>';
echo 'clocked out: '.$finish_time.'<br>';
echo 'breaks: '.$total_breaks.' minutes<br>';
echo 'hours worked: '.number_format($hours, 2).'<br>';
?>

clocked in: 2015-09-23 22:00:00
clocked out: 2015-09-24 06:00:00
breaks: 30 minutes
hours worked: 7.50

Suggestion: Change your start_time and finish_time fields to DATETIME so you won't have a problem if someone works through midnight to the next day like in the code I used.

NOTE: If you use strtotime without a date specified like strtotime(09:00:00) ... the current date will be automatically added to form a complete DateTime. BUT if anyone works through midnight the calculation will yield a negative number.

IF no one works through midnight you can just use the Time as your data like so...

<?php
$start_time = '09:00:00';
$finish_time = '17:00:00';
$starttime = strtotime($start_time);
$endtime = strtotime($finish_time);
etc...
?>

Answered By - Kuya