I can not convert seconds to minutes

Navigation
0

I'm doing the following query: 

    SELECT SEC_TO_TIME(sum(Total_Segundos_Consumidos)/count(distinct IDTRANSACTIONS)) AS 'Tempo Médio' 
    FROM
    (SELECT IDTRANSACTIONS, 
    SUM(TIME_TO_SEC(TimeDiff(TIME_FORMAT(DATEFIM,'%H:%i'), 
                             TIME_FORMAT(DATEINI,'%H:%i')))) AS Total_Segundos_Consumidos
    FROM TEMPO
    GROUP BY IDTRANSACTIONS) AS Q

And it's not working.

The following error appears:

  

Bad format for Time '-05: 46: 27.2727' in column 1

This is because, when you run only the command inside, it has negative numbers:

  

-80340, -86040

Examples in link

My goal is to take care of the time and take an average of the time. In the link above, I put the times of one of the users to serve as an example.

    
asked by anonymous 04.09.2018 / 15:03

3 answers

1

I was able to solve the problem with the following query: 

    SELECT TIME_FORMAT((TIMESTAMP(SEC_TO_TIME(round(SUM(TIMESTAMPDIFF(second,DATEFIM,DATEINI))/count(*),0)))),'%H:%i:%s') as 'Tempo Médio de Emissão'
    FROM
    (SELECT IDTRANSACTIONS,
    DATEFIM, DATEINI
    FROM TEMPO
    GROUP BY IDTRANSACTIONS) AS Q

Thank you to those who have tried to help. :)

Note: In SQLFiddle, the result is getting wrong, but in Dbeaver (where I'm running), it's correct.

    
04.09.2018 / 15:26
2

How to do

A simpler way would be to use the SEC_TO_TIME function with TIMESTAMPDIFF : 

SELECT IDTRANSACTIONS, 
       SEC_TO_TIME(TIMESTAMPDIFF(second, DATEINI, DATEFIM)) AS 'Tempo Médio de Emissão'
FROM TEMPO
GROUP BY IDTRANSACTIONS, DATEINI, DATEFIM;

Explaining

The function TIMESTAMPDIFF , other than TIMEDIFF , can parameterize the return to seconds (second): TIMESTAMPDIFF(second, DATEINI, DATEFIM) .

Then the SEC_TO_TIME function will convert the seconds to the format " HH: MM: SS ".

See working at db-fiddle

References

Date and Time Functions

    
17.09.2018 / 17:19
1

If you extract the minutes from the time difference, if you want the minutes as the end result this should serve: 

EXTRACT(minute from TIMEDIFF(DATEFIM, DATEINI))
    
04.09.2018 / 15:08
What code to print only the first number of a value? ex: only 8 of 8372883 execute JQuery calculation for each corresponding div