EC_TO_TIME() returns a TIME, is constrained to the range of the TIME data type, and a warning occurs for arguments resulting in values outside of that range
1. Solution
DROP FUNCTION IF EXISTS BIGTIMEDIFF; DELIMITER $$ CREATE FUNCTION `test`.`BIGTIMEDIFF` (end_time VARCHAR(64), start_time VARCHAR(64)) RETURNS INT(10) DETERMINISTIC BEGIN DECLARE ret_val INT(10); SELECT DATEDIFF(end_time, start_time) * 24 + EXTRACT(HOUR FROM end_time) - EXTRACT(HOUR FROM start_time) INTO ret_val ; RETURN ret_val; END$$ DELIMITER ; -- Example calls SELECT BIGTIMEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00'); SELECT BIGTIMEDIFF(CURDATE() + INTERVAL 1 YEAR, NOW());
2.
DROP FUNCTION IF EXISTS HOUR_MINUTES; DELIMITER $$ CREATE FUNCTION HOUR_MINUTES (SECS BIGINT) RETURNS CHAR(50) DETERMINISTIC BEGIN DECLARE HOUR CHAR(40); DECLARE MINUTES CHAR(10); SET HOUR = ROUND(CASE WHEN MOD(SECS,3600) > 3570 THEN TRUNCATE((SECS/ 3600), 0) + 1 ELSE TRUNCATE((SECS/ 3600), 0) END); SET MINUTES = ROUND(CASE WHEN MOD(SECS,3600) >3570 THEN 0 ELSE MOD(SECS,3600)/60 END); IF MINUTES < 10 THEN SET MINUTES = CONCAT( '0', MINUTES); END IF; RETURN CONCAT( HOUR, ':', MINUTES); END$$ DELIMITER ; -- Example calls mysql> SELECT HOUR_MINUTES('3904200'); +-------------------------+ | HOUR_MINUTES('3904200') | +-------------------------+ | 1084:30 | +-------------------------+ 1 row in set (0.00 sec)
3.
SELECT DATEDIFF('2010-01-01 00:00:00', '2009-01-01 00:00:00') * 24 + EXTRACT(HOUR FROM '2010-01-01 00:00:00') - EXTRACT(HOUR FROM '2009-01-01 00:00:00')
4. Show
SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS;