Change an integer (seconds) to Hours:MM:SS format

SecondsHOURSMMSS.sql

-- Similar to the MySQL function SEC_TO_TIME() except this
--  function can handle hours greater than 23. 

-- IBM Db2 for i
-- UDF
-- Accepts an integer (seconds) and returns a time value formatted as 'HOURS:MM:SS',
CREATE OR REPLACE FUNCTION libary/SECS_TO_HOURSMMSS (
            Duration INT
    )
    RETURNS VARCHAR(20)
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    BEGIN
        DECLARE HH INT;
        DECLARE MM INT;
        DECLARE SS INT;
        DECLARE HH1 VARCHAR(10);
        DECLARE MM1 VARCHAR(2);
        DECLARE SS1 VARCHAR(2);
        
        -- If duration in seconds less than 0 or greater than
        --  than maximum range of an integer then return NULL.
        IF Duration < 0
            OR Duration > 2147483647 THEN
            RETURN NULL;
        ELSE
            -- Calculate hours, minutes and seconds
            SET HH = FLOOR(duration / 3600);
            SET MM = FLOOR((duration-HH * 3600) / 60);
            SET SS = duration-(HH * 3600) - (MM * 60);
            
            -- Format each calculated value
            IF (HH < 10) THEN
                SET HH1 = '0' CONCAT VARCHAR(HH);
            ELSE
                SET HH1 = VARCHAR(HH);
            END IF;
            IF (MM < 10) THEN
                SET MM1 = '0' CONCAT VARCHAR(MM);
            ELSE
                SET MM1 = VARCHAR(MM);
            END IF;
            IF (SS < 10) THEN
                SET SS1 = '0' CONCAT VARCHAR(SS);
            ELSE
                SET SS1 = VARCHAR(SS);
            END IF;
            -- Join formatted values and return
            RETURN RTRIM(HH1) CONCAT ':' CONCAT RTRIM(MM1) CONCAT ':' CONCAT SS1;
        END IF;
    END; 
    COMMENT ON SPECIFIC FUNCTION library/SECS_TO_HOURSMMSS 
	IS 'Convert a duration in seconds to HOURS:MM:SS format' ;      

    COMMENT ON PARAMETER SPECIFIC FUNCTION library/SECS_TO_HOURSMMSS 
     ( DURATION IS 'integer - duration in seconds ' ) ; 
    
-- IBM Db2 for i
-- Table function
-- Accepts an integer (seconds) and returns 3 columns; hours, minutes, seconds
CREATE OR REPLACE FUNCTION library/SECS_TO_HOURSMMSS_table (
            Duration INT
    ) 
    
     RETURNS TABLE (
        hours INT,
        minutes SMALLINT,
        seconds SMALLINT )
        LANGUAGE SQL
        DETERMINISTIC
        READS SQL DATA
    BEGIN
        DECLARE HH INT;
        DECLARE MM SMALLINT;
        DECLARE SS SMALLINT;
        
            SET HH = FLOOR(duration / 3600);
            SET MM = FLOOR((duration-HH * 3600) / 60);
            SET SS = duration-(HH * 3600) - (MM * 60);
       Return  with x  (hours, minutes, seconds) as (values(HH,  MM, SS) )
        select hours, minutes, seconds from x;
        
     END;
       COMMENT ON SPECIFIC FUNCTION library/SECS_TO_HOURSMMSS_table 
	IS 'Convert a duration in seconds to hours, minutes, seconds columns' ;   

        COMMENT ON PARAMETER SPECIFIC FUNCTION library/SECS_TO_HOURSMMSS_table 
         ( DURATION IS 'integer - duration in seconds ' ) ;  
         
    
 -- PostgreSQL
 -- Accepts an integer (seconds) and returns a time value formatted as 'HOURS:MM:SS',
 CREATE or REPLACE FUNCTION SECS_TO_HOURSMMSS(duration INT) Returns varchar(20) as $$
Declare
HH INT;
MM INT;
SS INT;
HH1 VARCHAR(10);
MM1 VARCHAR(2);
SS1 VARCHAR(2);
Begin
-- If duration in seconds less than 0 or greater than
        --  than maximum range of an integer then return NULL.
        IF Duration < 0
            OR Duration > 2147483647 THEN
            RETURN NULL;
        ELSE
            -- Calculate hours, minutes and seconds
             HH = FLOOR(duration / 3600);
             MM = FLOOR((duration-HH * 3600) / 60);
             SS = duration-(HH * 3600) - (MM * 60);
            
            -- Format each calculated value
            IF (HH < 10) THEN
                 HH1 = '0' ||  HH;
            ELSE
                 HH1 = HH;
            END IF;
            IF (MM < 10) THEN
                 MM1 = '0' || MM;
            ELSE
                 MM1 = MM;
            END IF;
            IF (SS < 10) THEN
                 SS1 = '0' || SS;
            ELSE
                 SS1 = SS;
            END IF;
            -- Join formatted values and return
            RETURN RTRIM(HH1) ||  ':' ||  RTRIM(MM1) ||  ':' ||  SS1;
        END IF;
End; $$
LANGUAGE plpgsql;