Result when converting minutes to hours

Question

I have a query where I have an example that gives the correct result and the other one does not when converting minutes to hours.

1st example in minutes:

SELECT  A.Colaborador,
        SUM(A.`Horas Consumidas`) AS `Total Horas`    
FROM
(SELECT centrodb.utentes.codigoutente,
        centrodb.utentes.nome,
        centrodb.utentes.descricaovalencia,
        centrodb.registoFisioterapia.`DataRegisto`,     
        centrodb.registoFisioterapia.`Data`,
        Inicio,
        Fim,
        centrodb.colaboradores.Nome AS Colaborador,
        TIME_TO_SEC(TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))/60 AS `Horas Consumidas`    
FROM centrodb.registoFisioterapia LEFT OUTER JOIN centrodb.utentes    
ON centrodb.utentes.Id = centrodb.registoFisioterapia.utente    
LEFT OUTER JOIN colaboradores    
ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome    
where centrodb.registoFisioterapia.`Data` between '2018-04-26' And '2018-05-8' AND centrodb.colaboradores.Nome = 'TÂNIA LOPES') AS A    
GROUP BY A.Colaborador

Result: 1650 minuots

1st example converted to hours:

SELECT  A.Colaborador,
        TIME_FORMAT(SEC_TO_TIME(SUM(A.`Horas Consumidas`)),'%i:%s') AS `Total Horas`

FROM
(SELECT centrodb.utentes.codigoutente,
        centrodb.utentes.nome,
        centrodb.utentes.descricaovalencia,
        centrodb.registoFisioterapia.`DataRegisto`,     
        centrodb.registoFisioterapia.`Data`,
        Inicio,
        Fim,
        centrodb.colaboradores.Nome AS Colaborador,
        TIME_TO_SEC(TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))/60 AS `Horas Consumidas`    
FROM centrodb.registoFisioterapia LEFT OUTER JOIN centrodb.utentes    
ON centrodb.utentes.Id = centrodb.registoFisioterapia.utente    
LEFT OUTER JOIN colaboradores    
ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome    
where centrodb.registoFisioterapia.`Data` between '2018-04-26' And '2018-05-8' AND centrodb.colaboradores.Nome = 'TÂNIA LOPES') AS A    
GROUP BY A.Colaborador

Result: 27:30

2nd example in minutes where the wrong time is:

SELECT  A.Colaborador,
        SUM(A.`Horas Consumidas`) AS `Total Horas`    
FROM
(SELECT centrodb.utentes.codigoutente,
        centrodb.utentes.nome,
        centrodb.utentes.descricaovalencia,
        centrodb.registoFisioterapia.`DataRegisto`,     
        centrodb.registoFisioterapia.`Data`,
        Inicio,
        Fim,
        centrodb.colaboradores.Nome AS Colaborador,
        TIME_TO_SEC(TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))/60 AS `Horas Consumidas`    
FROM centrodb.registoFisioterapia LEFT OUTER JOIN centrodb.utentes    
ON centrodb.utentes.Id = centrodb.registoFisioterapia.utente    
LEFT OUTER JOIN colaboradores    
ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome    
where centrodb.registoFisioterapia.`Data` between '2018-03-26' And '2018-04-25' AND centrodb.colaboradores.Nome = 'TÂNIA LOPES') AS A    
GROUP BY A.Colaborador

Result: 3840 minutes

2nd example converted to hours:

SELECT  A.Colaborador,
        TIME_FORMAT(SEC_TO_TIME(SUM(A.`Horas Consumidas`)),'%i:%s') AS `Total Horas`    
FROM
(SELECT centrodb.utentes.codigoutente,
        centrodb.utentes.nome,
        centrodb.utentes.descricaovalencia,
        centrodb.registoFisioterapia.`DataRegisto`,     
        centrodb.registoFisioterapia.`Data`,
        Inicio,
        Fim,
        centrodb.colaboradores.Nome AS Colaborador,
        TIME_TO_SEC(TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))/60 AS `Horas Consumidas`    
FROM centrodb.registoFisioterapia LEFT OUTER JOIN centrodb.utentes    
ON centrodb.utentes.Id = centrodb.registoFisioterapia.utente    
LEFT OUTER JOIN colaboradores    
ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome    
where centrodb.registoFisioterapia.`Data` between '2018-03-26' And '2018-04-25' AND centrodb.colaboradores.Nome = 'TÂNIA LOPES') AS A    
GROUP BY A.Colaborador

Result: 04:00, but should result 64:00.

Can someone help me solve this problem?

Answer 1

This is not only an error in the code, but also a slight confusion with the result of the function SEC_TO_TIME used to obtain the result. This confusion is partly caused by the fact that, as 1650 minutes correspond to 27h30m, also 1650 seconds correspond to 27m30s.

If you do not pay attention it seems that it is the same unit of time. It does not help here the fact of the mask '%i:%s' hide the time component of the final result.

Let’s go in parts: In its subquery calculates the difference between the beginning and the end of the physical therapy session. This difference is converted in seconds by the TIME_TO_SEC function and then converted in minutes. Here:

TIME_TO_SEC(TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))/60 

In the external SELECT, it sums the minutes of each collaborator but returns to use the SEC_TO_TIME function that expects an input in seconds and not minutes. Here’s the problem.

Knowing this, it is easy to notice that in the result you call "first example converted to hours" comes 0h27m30s. And in the result you call "2nd example converted to hours" comes 04:00.

SEC_TO_TIME(3840) = 3840 / 60 / 60 = !!64!! / 60 ~= 1h04m00

The problem is that your mask causes the final result to only show 04:00. Without the mask, you would easily realize where the error is.

See the SQL Fiddle. Here is also an alternative to Fiddle (which once again is down) rextester

Now that we realize why of the various results (I hope, because the explanation is a little confusing), to fix your code and get the total hours for each employee can for example, remove the conversion to minutes and always work with the seconds and only at the end convert to TIME.

Applying to your code (note that the SUM function passed inside since it was not using any of the other fields, to simplify a bit), would be:

SELECT  A.Colaborador,
        SEC_TO_TIME(Total_Segundos_Consumidos) AS `Total Horas`    
FROM
(
    SELECT centrodb.colaboradores.Nome AS Colaborador,
           SUM(TIME_TO_SEC(TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))) AS Total_Segundos_Consumidos    
      FROM centrodb.registoFisioterpia 
      LEFT OUTER JOIN colaboradores    
        ON centrodb.colaboradores.codigo = centrodb.registoFisioterapia.Nome    
     WHERE centrodb.registoFisioterapia.`Data` between '2018-03-26' AND '2018-04-25' 
       AND centrodb.colaboradores.Nome = 'TÂNIA LOPES'
     GROUP BY centrodb.colaboradores.Nome 
) AS A