Currently I'm work on with the attendance management system.i calculate how many hours work done in employees.i already calculate the how much hours working in day and it store in the mysql database.
$totaltime = (strtotime($time_out) - strtotime($time_in));
$hours = sprintf('%02d', intval($totaltime / 3600));
$seconds_remain = ($totaltime - ($hours * 3600));
$minutes = sprintf('%02d', intval($seconds_remain / 60));
$seconds = sprintf('%02d' ,($seconds_remain - ($minutes * 60)));
$final = '';
if ($time_in == '' || $time_out == '')
$final = '';
$final .= $hours.':'.$minutes.':'.$seconds;
$time_in = 08:09:57
$time_out = 16:04:50
$final = 07:54:53 (total working hours)
now i try to get the current month total working time for each employee.how do get sum of the $final using php?
sample data of the month_data
Emp_no Date Time_in Time_out Total_hours TranID
23 2019-08-01 07:54:40 16:01:40 08:07:00 1
23 2019-08-02 07:42:35 16:02:53 08:20:18 2
i want get the sum of the Total_hours for related one employee
You can take a look at a query somewhat like this
SELECT SEC_TO_TIME(SUM(`Total_hours`) ) FROM `month_data` GROUP BY `Emp_no`;
there is a simple SUM function which can do this for you, it returns the total time in seconds though.
In order to turn that into readable time you can use the MySQL function SEC_TO_TIME.
If the said column is not a TIME column you can CAST it to be handled as this type of column using CAST() the needed SQL would look something like
SELECT SEC_TO_TIME(SUM(CAST(`Total_hours` AS TIME)) ) FROM `month_data` GROUP BY `Emp_no`;
My suggestion would be to change the column type to TIME though.
I was under the assumption that SUM() would be smart enough to convert the time to seconds and come up with the correct sum of the given times.
Not sure why yet but this is not the case, therefore you need to convert the given times to seconds first.
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`Total_hours`)) ) FROM `month_data` GROUP BY `Emp_no`;
Now I have not tested this but TIME_TO_SEC() seems to accept VARCHAR just fine so need to CAST() the column anymore.