Last active
February 8, 2023 08:49
-
-
Save NaWer/8333736 to your computer and use it in GitHub Desktop.
SEC_TO_TIME and TIME_TO_SEC is constrained to the range of the TIME data type (-838:59:59 to 838:59:59). This MySQL function overtakes this limit.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP FUNCTION IF EXISTS BIG_SEC_TO_TIME; | |
DELIMITER $$ | |
CREATE FUNCTION BIG_SEC_TO_TIME(SECS BIGINT) | |
RETURNS TEXT | |
READS SQL DATA | |
DETERMINISTIC | |
BEGIN | |
DECLARE HEURES TEXT; | |
DECLARE MINUTES CHAR(5); | |
DECLARE SECONDES CHAR(5); | |
IF (SECS IS NULL) THEN RETURN NULL; END IF; | |
SET HEURES = FLOOR(SECS / 3600); | |
SET MINUTES = FLOOR((SECS - (HEURES*3600)) / 60); | |
SET SECONDES = MOD(SECS, 60); | |
IF MINUTES < 10 THEN SET MINUTES = CONCAT( "0", MINUTES); END IF; | |
IF SECONDES < 10 THEN SET SECONDES = CONCAT( "0", SECONDES); END IF; | |
RETURN CONCAT(HEURES, ":", MINUTES, ":", SECONDES); | |
END; | |
$$ | |
DELIMITER ; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP FUNCTION IF EXISTS BIG_TIME_TO_SEC; | |
DELIMITER $$ | |
CREATE FUNCTION BIG_TIME_TO_SEC(TIME TEXT) | |
RETURNS BIGINT | |
DETERMINISTIC | |
READS SQL DATA | |
BEGIN | |
DECLARE HEURES TEXT; | |
DECLARE MINUTES CHAR(5); | |
DECLARE SECONDES CHAR(5); | |
IF (TIME IS NULL) THEN RETURN NULL; END IF; | |
SET HEURES = SUBSTRING_INDEX(TIME, ":", 1); | |
SET MINUTES = SUBSTRING(TIME FROM -5 FOR 2); | |
SET SECONDES = SUBSTRING(TIME FROM -2 FOR 2); | |
RETURN CAST(HEURES AS UNSIGNED INTEGER)*3600 + CAST(MINUTES AS UNSIGNED INTEGER)*60 + CAST(SECONDES AS UNSIGNED INTEGER); | |
END; | |
$$ | |
DELIMITER ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thank you for these functions!!!