Derived Field (sec_to_time) – SharpenCX

Overview

This article walks through building a derived field that calculates the total duration of a customer's chat interaction, from the moment they entered the queue to the moment the interaction ended. This is similar to Agent Handle Time, but measured from the customer's perspective.

This example introduces the sec_to_time function, which is required when adding multiple time-based fields together in a derived field.

Planning the Formula

The customer's total interaction duration is the sum of every time segment they experience:

Component	Description
Wait Time	How long the customer waited before being connected to an agent
Agent Talk Time	How long the agent spent actively handling the interaction
Agent Hold Time	How long the customer was placed on hold by the agent
Switch Hold Time	How long the customer was on hold due to the agent switching between interactions

Chat Duration (Customer) = Wait Time + Agent Talk Time + Agent Hold Time + Switch Hold Time

Understanding the sec_to_time Function

All four fields store time as raw seconds. When you add multiple time-based fields together without sec_to_time, the result is returned as a plain number — not in a readable hh:mm:ss format.

For example, a result of 588 is technically correct, but 00:09:48 is far easier to interpret in a report. The sec_to_time function handles this automatically:

Step 1		Step 2		Result
Accept the sum of all time fields (in seconds)	→	Perform the calculation	→	Output formatted as hh:mm:ss

Any derived field that adds or combines multiple time-based fields should use sec_to_time to ensure the output is readable.

Building the Derived Field

Setup

Open the Derived Fields Manager

Access it from the sidebar, select Queue Segment Activity as the data source, and click + New Derived Field.

Name the field

Enter "Chat Duration (Customer)" as the field name.

Formula Steps

Add a group

Click Add Group. Confirm the red outline is around the group before continuing; all four fields will be placed inside it.

Add the four time fields

With the group selected, click Add Field and select Wait Time. Then click + and add the remaining three fields: Agent Talk Time, Agent Hold Time, and Switch Hold Time—with a + operator between each one.

Apply the sec_to_time group function

With the red outline around the group, open the Group Functions drop-down and select sec_to_time. Insights will sum the four fields first, then format the result as hh:mm:ss.

Save and close

Click Save, then close the Derived Fields Manager. The field is now available in Queue Segment Activity and can be added to any report like a standard field.

Variation: Average Chat Duration

Because summary functions cannot be applied to derived fields in a report, you can embed an average into the derived field itself. Wrap the entire sec_to_time group in an outer group and apply the AVG group function to it.

The field will then return the average “Chat Duration (Customer)” across all interactions in the report, rather than a per-interaction value.