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

Question

SGBD: MYSQL

Problem: I am adding HOURS to a TIME field type and the expected result is above 838:59:59, but is visible only up to this limit. How to solve?

SELECT      he.empresa,
            SEC_TO_TIME(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;

Upshot:

1|838:59:59|

Answer 1

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

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

I created the function below, then it is much easier to use in 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 ;

That way just 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 that there are solutions that are more optimized, but I believe that there is no other way to perform the calculation with TIME without separating the fields. If anyone has another way to make the conversion, I’ll keep an eye on that topic. :)

Answer 2

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

This function will return H:m, I don’t know what format you need, but if you need to take the seconds replace the Return with:

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