timediff () limited to 838: 59: 59, how to solve?

When the SEC_TO_TIME () is used the value returned is of type TIME , and the restrictions for this data type are applied (Minimum: '-838: 59: 59' and maximum: '838: 59: 59').

As in your case there is a possibility of returning a value beyond the limit, one way is to convert hour, minute and second separately.

I created the function below, it is much easier to use in the queries.

DELIMITER //                                                                              

CREATE FUNCTION SEC_TO_TIMEB (in_seconds bigint) RETURNS VARCHAR(15)                                                                                                           
BEGIN                                                                                     
    DECLARE hours VARCHAR(9);                                                 
    DECLARE minutes CHAR(2);                                               
    DECLARE seconds CHAR(2);                                               

    SET hours   := FLOOR(in_seconds / 3600);                                              
    SET hours   := IF(hours < 10,CONCAT('0',hours),hours);                                

    SET minutes := FLOOR(MOD(in_seconds,3600) / 60);                                      
    SET minutes := IF(minutes < 10,CONCAT('0',minutes),minutes);                          

    SET seconds := MOD(MOD(in_seconds,3600),60);                                          
    SET seconds := IF(seconds < 10,CONCAT('0',seconds),seconds);

    RETURN CONCAT(hours,':',minutes,':',seconds);                                         
END //                                                                                    

DELIMITER ;

In this way, you just have to use the new function in your query.

SELECT      he.empresa,
            SEC_TO_TIMEB(SUM(TIME_TO_SEC(CASE WHEN timediff(hr.hora_final,hr.hora_inicial) < 0 THEN TIMEDIFF(CONCAT('1990-01-02', ' ',hr.hora_final),CONCAT('1990-01-01', ' ',hr.hora_inicial)) ELSE timediff(hr.hora_final,hr.hora_inicial) END))) AS Soma
FROM        hora_extra AS he
INNER JOIN  horario_extra AS hr ON hr.codigo = he.cod_extra
WHERE       he.empresa = 1
ORDER BY    he.empresa;

I imagine there are solutions that are more optimized, but I do not think there is any other way to do the calculation with TIME without separating the fields. If anyone has another way to do the conversion, I'll keep an eye on that topic. :)

This is a MySQL bug, here you will find information about this bug and a function to solve this problem bugs.mysql.com

This function will return H: m, I do not know what format you need, but if you need to get the seconds, replace the return with:

RETURN CONCAT( HOUR, ':', MINUTES, ':', LPAD(MOD(SECS,60),2, '0'));