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. :)
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.).– Bacco
The expected result is hh:mm:ss. Nothing complicated, just needed the hours summed. : D
– mcardoso