 Tags  # how to get sum of the total time

By : Piotr Poprawski
Date : May 04 2020, 04:53 PM

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 = '';
}
else
{
\$final .= \$hours.':'.\$minutes.':'.\$seconds;
}
``````

for example

``````\$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 Answer :

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`.

edit

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.

edit 2

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. Related Posts : 