I can’t convert seconds into minutes

Question

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 running only the inside command brings negative numbers:

-80340, -86040

Examples in http://sqlfiddle.com/#! 9/8cedc9/11

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

Answer 1

How to do

A simpler way would be using the function SEC_TO_TIME 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, you can parameterize the return to seconds (Second): TIMESTAMPDIFF(second, DATEINI, DATEFIM).

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

---

See working on db-fiddle

---

References

Date and Time Functions

Answer 2

For future reference:

A query that seems simpler, exploits a little more the bank resources and decreases the overhead to solve the above problem would be:

SELECT
  TIME_FORMAT( SEC_TO_TIME( AVG( TIMESTAMPDIFF(SECOND,  
                                               DATEINI,
                                               DATEFIM))), 
               '%H:%i') AS Total_Segundos_Consumidos
FROM TEMPO;

See working on sqlfiddle.

Reference: Response from the Rbz.

Answer 3

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

EXTRACT(minute from TIMEDIFF(DATEFIM, DATEINI))

Answer 4

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

I thank those who tried to help. :)

Note: In Sqlfiddle, the result is getting wrong, but in Dbeaver (where I am running), it is correct.