Format time result

Question

Format time result

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have the following fields:

IwouldliketoformattheresultofmyfollowingQuery:

SELECTTIMEDIFF(primeiraSaida,primeiraEntrada)+TIMEDIFF(segundaSaida,segundaEntrada)FROMpontoentradasaidaWHEREprimeiraEntradaBETWEEN'2018-09-13'AND'2018-09-14';

Wheretheoutputisthis:

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

Thank you in advance

mysql

asked by anonymous 13.09.2018 / 13:45

2 answers

2

You can use the TIME_FORMAT :

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

13.09.2018 / 14:56

Redirect failure Laravel store an array of objects in a local storage with JS

score 1 · Accepted Answer

So it turns out that you can not convert the time in this way you did, since the TIMEDIFF function already returns H:i:s format.

Then when you use the + operator to add, 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. And finally converted to 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';