Xrhrft

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