MySQL – Convert Seconds into HH:MM:SS Format

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:

enter image description here

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?

Best Answer

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.