Difference between two dates with time greater than 24h?

4

I have the following query:

SQLFiddle

It's past 24h, it clears time and starts counting again.

I need to make the difference between the hours, counting that they have to go beyond 24h.

For example, on the first line of the result I have 2018-09-03T10: 35: 16Z. Taking the difference between today (2018-09-05T14: 27: 39Z) and this day, it would give the following result:

51:55:28 which is the same as 2 days, 3 hours, 55 minutes and 28 seconds

But the result is only being 03:55:28

How can I make this date difference greater than 24h?

    
asked by anonymous 05.09.2018 / 16:34

2 answers

4

It is not a great solution, but you can only take the minutes and seconds that are already calculated and concatenate with a TIMESTAMPDIFF in hours.

SQLFiddle

SQL:

SELECT 
    date_ini,
    NOW(),
    CONCAT(
        TIMESTAMPDIFF(HOUR, date_ini, NOW()),
        TIME_FORMAT(
            TIMESTAMP(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, date_ini, NOW()))),
            ':%i:%s'
        )
    ) as "Tempo de Análise"
from TEMPO;
    
05.09.2018 / 17:00
2

Just as a complement to the answer I use the following way:

SELECT DATEINI, NOW(), 
CONCAT(TRUNCATE(TIMESTAMPDIFF(MINUTE, DATEINI, NOW())/60,0), 
':',TIMESTAMPDIFF(MINUTE, DATEINI, NOW())%60 ,
':', TIMESTAMPDIFF(second, DATEINI, NOW())%60 ) 
AS 'Tempo de Análise'
FROM TEMPO
    
05.09.2018 / 17:10