Convert everything to mysql in hours or minutes

Question

I have a table with the two fields Start and End of Tipo de dados Time. Example of records I have in the table:

• 09:30:00 10:00:00
• 10:00:00 11:00:00
• 11:00:00 12:00:00

As I show, I have full hours and half hours, as you can only have 20 minutes or 45 minutes for example.

Now I plan to make the hours difference and then add up the total hours per customer.

The problem is that when I don’t have full hours, for example I have 5 records of the same client of 30 minutes, so it’s 2:30 minutes, the query shows the result as 1:50, because it’s adding up 30 five times.

Query:

SELECT C.codigoutente,
       C.nome AS Utente,
       C.descricaovalencia AS Valência,
       REPLACE(CAST(SUM(SEC_TO_TIME(C.Minutos))/100 AS DECIMAL (15,2)), '.', ':') AS `Horas Consumidas`,
       CONVERT(CONCAT(CAST(SUM(C.Valor) AS DECIMAL (15,2)), '€'), char(8))  AS `Valor Total`

FROM 
(SELECT B.codigoutente,
        B.nome,
        B.descricaovalencia,
        B.`Data`,
        B.Minutos,
        IF(B.nome = 'CLASSE', (B.Minutos*10)/60, (B.Minutos*12)/60) AS Valor

FROM 
(SELECT A.codigoutente,
        A.nome,
        A.descricaovalencia,
        A.`Data`,
        A.`Horas Consumidas`,
        CAST(TIME_TO_SEC(A.`Horas Consumidas`)/60 AS DECIMAL(15,0)) AS Minutos       

FROM 
(SELECT centrodb.registoFisioterapia.id,
        centrodb.utentes.codigoutente,
        centrodb.utentes.nome,
        centrodb.utentes.descricaovalencia,
        centrodb.registoFisioterapia.`Data`,
        Inicio,
        Fim,
        centrodb.colaboradores.Nome AS Colaborador,
        TimeDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')) 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) AS A) AS B

) AS C

GROUP BY C.codigoutente, C.nome, C.descricaovalencia

The correct result then should be 2:30 minutes and not 1:50 minutes. Someone can help?

Altering:

SELECT B.codigoutente,
       B.nome,
       B.descricaovalencia AS Valência,
       SUM(B.Minutos) AS `Horas Consumidas`,
       CONVERT(CONCAT(CAST(SUM(B.Valor) AS DECIMAL (15,2)), '€'), char(8))  AS `Valor Total`

FROM 
(SELECT A.codigoutente,
        A.nome,
        A.descricaovalencia,
        A.`Data`,
        A.`Minutos`,
        IF(A.nome = 'CLASSE', (A.`Minutos`*10), (A.`Minutos`*12)) AS Valor

FROM 
(SELECT centrodb.registoFisioterapia.id,
        centrodb.utentes.codigoutente,
        centrodb.utentes.nome,
        centrodb.utentes.descricaovalencia,
        centrodb.registoFisioterapia.`Data`,
        Inicio,
        Fim,
        centrodb.colaboradores.Nome AS Colaborador,
        CAST(TIME_TO_SEC(TiMEDiff(TIME_FORMAT(Fim,'%H:%i'), TIME_FORMAT(Inicio,'%H:%i')))/60/60 AS DECIMAL(15,2)) AS `Minutos`

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) AS A) AS B

GROUP BY B.codigoutente, B.nome, B.descricaovalencia

Answer 1

How to do

You can use the TIME_TO_SEC, so it converts your hour into seconds, and you divide by 60 to minutes, and again by 60 to hours:

Example:

SELECT (TIME_TO_SEC(TIME_FORMAT('22:30','%H:%i')) - TIME_TO_SEC(TIME_FORMAT('10:00','%H:%i')))/60/60;

// ou

SELECT TIME_TO_SEC(TimeDiff(TIME_FORMAT('22:00','%H:%i'), TIME_FORMAT('10:30','%H:%i')))/60/60;

In your query:

SELECT (TIME_TO_SEC(TIME_FORMAT(Fim,'%H:%i')) - TIME_TO_SEC(TIME_FORMAT(Inicio,'%H:%i')))/60/60;

// ou

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

---

Complement

In addition, there are other functions, sometimes they may be useful:

Time difference:

Hour(TIMEDIFF(Fim,Inicio)) as Horas

Minute difference:

Minute(TIMEDIFF(Fim,Inicio)) as Minutos

Seconds apart:

Second(TIMEDIFF(Fim,Inicio)) as Segundos

---

Useful link: timestampdiff

---

UPDATE 1

As seen by CHAT, your Bank will bring the hours in quantity(2,5) and not in the display format (2:30).

Converting into the SELECT:

TIME_FORMAT(SEC_TO_TIME(SUM(A.Minutos)),'%i:%s') AS Minutos,

Converting in PHP:

If it’s never past 24 hours, you can use it like this:

echo date('H:i', mktime(0,$minutos*60)); 
ou 
echo date('H:i', mktime(0,$horas));

If you can pass 24 hours, you will have to do it manually (there are several ways):

function convertHoras($horas, $formato = '%02d:%02d') {

   $qHoras = floor($horas);
   $qMinutos = ($horas % 60);

   return sprintf($formato, $qHoras, $qMinutos);
}

Source