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

Asked

Viewed 2,105 times

6

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|
  • Wouldn’t it be the case to just use TIMESTAMPDIFF() ?https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff Explain in more detail your goal (if you need the result in minutes, seconds, how you will use it, etc.).

  • The expected result is hh:mm:ss. Nothing complicated, just needed the hours summed. : D

2 answers

8


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. :)

  • Excellent! Perfect solution!

1

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'));

  • 2

    Actually it is not a bug, it is a limitation of the TIME field itself that stores only 3 bytes. It is the same thing as trying to add a value greater than 16777215 in a MEDIUMINT field.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.