Convert everything to hours or everything in minutes in mysql

Navigation
2

I have a table with the two Start and End fields 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 want to make the time difference and then add up the total hours per customer.

The problem is that when I do not have whole 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 is adding 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 should then be 2:30 minutes and not 1:50 minutes. Can anyone help?

Change: 

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
    
asked by anonymous 29.03.2018 / 11:35

1 answer

3

How to do

You can use TIME_TO_SEC , so it converts your time into seconds, and you divide by 60 for minutes, and again for 60 for 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 useful:

Time Difference: 

Hour(TIMEDIFF(Fim,Inicio)) as Horas

Difference of minutes: 

Minute(TIMEDIFF(Fim,Inicio)) as Minutos

Difference of seconds: 

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 to SELECT : 

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

Converting to PHP:

If you never spend more than 24 hours, you can do so: 

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

If you can spend 24 hours, you 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);
}

Font

    
29.03.2018 / 12:19
Binding inside a CompositeCollection does not work More than one list in the same loop