Convert seconds into HH:MM:SS format in MySQL

I have a query where I’m pulling data from the Zultys VoIP Database, about employees and their call in/call out numbers, and the time they spent over the phone: Time in/Time out in seconds, and their phone extensions as well.

The query is below:

SELECT 
    IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION) AS Ext,
    SUM(IF(TRUNKGROUPNAME2 != 'itsp.Nextiva',
        IF(SESSIONIDTRANSFEREDTO IS NULL,
            Duration,
            HoldTimeSecs),
        0)) AS TimeIn,
    SUM(IF(TRUNKGROUPNAME2 = 'itsp.Nextiva',
        IF(SESSIONIDTRANSFEREDTO IS NULL,
            Duration,
            HoldTimeSecs),
        0)) AS TimeOut,
    SUM(IF(TRUNKGROUPNAME2 != 'itsp.Nextiva',
        1,
        0)) AS CallIn,
    SUM(IF(TRUNKGROUPNAME2 = 'itsp.Nextiva',
        1,
        0)) AS CallOut
FROM
    ((session
    INNER JOIN callsummary ON session.NOTABLECALLID = callsummary.NOTABLECALLID
        AND DATEDIFF(CURDATE(), STARTTIME) = 1)
    LEFT JOIN mxuser ON session.EXTENSIONID1 = mxuser.EXTENSIONID)
        LEFT JOIN
    mxuser AS mxuser_1 ON session.EXTENSIONID2 = mxuser_1.EXTENSIONID
WHERE
    SESSIONTYPE = 1 AND ANSWERED = 1
        AND (DIALPLANNAME NOT LIKE 'Local %'
        AND TRUNKGROUPNAME1 LIKE 'dev.%'
        AND TRUNKGROUPNAME2 = 'itsp.Nextiva')
        OR (TRUNKGROUPNAME1 = 'itsp.Nextiva'
        AND TRUNKGROUPNAME2 LIKE 'dev.%'
        AND CALLINGPARTYNO NOT LIKE '404%'
        AND CALLINGPARTYNO NOT LIKE '678%'
        AND CALLINGPARTYNO NOT LIKE '770%')
GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
HAVING Ext IS NOT NULL;

When I run this query above I get this result:

What I’m trying to achieve is to add TimeIn and TimeOut as total time and to convert seconds to this format HH:MM:SS.

Any thoughts?

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

see

• GET_FORMAT()
• SEC_TO_TIME()
• TIME_FORMAT()

and possibly others.

Not knowing the datatype of your values, I can’t be more specific.

I think SEC_TO_TIME function is what you need here if I correctly understood your request: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sec-to-time

You may read more about date and time functions reading this documentation: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

CREATE DEFINER=`root`@`localhost` FUNCTION `ConvertTimeDelta`(duration int) RETURNS 
varchar(20) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
 DECLARE time_delta VARCHAR(20);
 DECLARE date_format VARCHAR(20);
 SET date_format = IF (duration > 3599999, '%H:%i:%s', '%i:%s');
 SET time_delta = TIME_FORMAT(SEC_TO_TIME(duration/1000), date_format);
 RETURN time_delta;
END

For example:

• SELECT ConvertTimeDelta(4800240)
  result = 01:20:00
• SELECT ConvertTimeDelta(35076)
  result = 00:35