Result when converting from minutes to hours

Question

Result when converting from minutes to hours

Navigation

#1 by (5 votes)

8

I have a query in which I have one example that gives the correct result and the other not when converting from 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

Example 2 in minutes where you give the wrong times:

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 it should have resulted in 64:00.

Can anyone help me solve this problem?

mysql

asked by anonymous 08.05.2018 / 12:09

1 answer

Difference between scroll functions? Why are some string methods static?

score 5 · Accepted Answer

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

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

Then let's break it down: In your subquery, you calculate the difference between the beginning and the end of the physiotherapy session. This difference is converted in seconds by the TIME_TO_SEC function and then converted into minutes. Here:

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

In the outer SELECT, sum the minutes of each collaborator but returns to use the SEC_TO_TIME function that expects an input in seconds, not minutes. Here's the problem.

Knowing this, it is easy to see that in the result he calls "1st example converted to hours" comes 0h27m30s. And in the result that he calls "2nd example converted to hours" comes 4:00.

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

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

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

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

Applying to your code (note that the SUM function went indoors since it was not using any of the other fields, to simplify it a bit), it would look like:

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