Unable to get total records of date in php mysql-CodePudding

I am working with php and mysql i have following table "employee_time",where date is containing for "day/hour/minute"

id      em_id           diff
1       1170            1:2:0
2       1170            0:2:5
...
A

nd i want to get total of "diff" (varchar) whose id is ='1170' I tried with following query but giving me wrong result

SELECT SUM(diff) as total_time from employee_time where em_id='1170'

expected result should be

1:4:5

CodePudding user response：

You could use the substring_index function to extract (day, hour, minutes) values from the 'diff' string as the following:

select em_id, concat_ws(':', sum(d), sum(h), sum(m)) total_time
from
(
  select *,
   substring_index(diff,':', 1) d,
   substring_index(substring_index(diff,':', 2),':', -1) h,
   substring_index(diff,':', -1) m
 from employee_time
) t
where em_id = 1170

See demo