Format result of time

Question

I have the following fields:

I would like to format the result of my following Query:

SELECT TIMEDIFF(primeiraSaida, primeiraEntrada) + TIMEDIFF(segundaSaida, segundaEntrada)
FROM pontoentradasaida 
WHERE primeiraEntrada BETWEEN '2018-09-13' AND '2018-09-14';

Where the exit is being:

But I’d like you to leave: H:mm:ss or 8:59:68s or 9:00:08

Thank you in advance

Answer 1

You can use the function TIME_FORMAT:

SELECT TIME_FORMAT(
  TIMEDIFF(primeiraSaida, primeiraEntrada) + TIMEDIFF(segundaSaida, segundaEntrada))
  , '%H:%i') AS DIFERENCA

Answer 2

So it turns out that there’s no way to convert into time that way you did, since the function TIMEDIFF returns already in format H:i:s.

Hence when you use the + operator to add up, it is no longer a DATE or TIME field.

So we have to do some conversions. The time difference has to be converted in seconds and then summed up. And finally converted into time format again.

SEC_TO_TIME ( SUM ( TIME_TO_SEC ( TIMEDIFF ) + TIME_TO_SEC ( TIMEDIFF ) ) )

SQL

SELECT
    SEC_TO_TIME(
        SUM(
            TIME_TO_SEC(
                TIMEDIFF(
                    primeiraSaida,
                    primeiraEntrada
                )
            ) + TIME_TO_SEC(
                TIMEDIFF(
                    segundaSaida,
                    segundaEntrada
                )
            )
        )
    )
FROM
    pontoentradasaida
WHERE
    primeiraEntrada BETWEEN '2018-09-13'
AND '2018-09-14';