Skip to content

Instantly share code, notes, and snippets.

@NaWer
Last active February 8, 2023 08:49
Show Gist options
  • Save NaWer/8333736 to your computer and use it in GitHub Desktop.
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.
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 ;
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 ;
@rodrigozem
Copy link

Thank you for these functions!!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment