Convert time duration to HH:MM:SS

Learning Center Articles
, ,
1

Sharing a calculated field you can use to convert a numeric field (measured in seconds) into HH:MM:SS format (will become a string). You cannot do any math or date calculations while in this form as the result is a string, but it will work for display purposes (and will sort correctly if you place on an X-axis, table, etc).

concat(

ifelse({Average Talk Time} / 3600 < 10,concat('0',tostring(floor({Average Talk Time} / 3600))),tostring(floor({Average Talk Time} / 3600)))

,':',

ifelse(({Average Talk Time} % 3600)/60 < 10,concat('0',tostring(floor(({Average Talk Time} % 3600)/60))),tostring(floor(({Average Talk Time} % 3600)/60)))

,':',

ifelse({Average Talk Time} % 60 < 10,concat('0',tostring(floor({Average Talk Time} % 60))),tostring(floor({Average Talk Time} % 60)))

)
7 Likes
2

Hi Jesse,
We are using a variation of what you posted here (thank you), and it works well (and similar to your Average Talk Time example, using it for Contact Center type analysis dashboards).

However we are using it in many Table visuals - normal table setup, with 1 or possibly 2 group-by columns, and then a bunch of value columns (often 4-5 columns of which need to be in HH:MM:SS format) on the right.

While the approach works for displaying at a row/group-by level, if you are trying to have totals on your tables, due to the H:M:S value ending up as a string, it doesn’t total (you end up with one of the random H:M:S string values from the column being totaled in your total line). Additionally, if the table ends up with 4-5 H:M:S columns, the resulting “totaling” Athena SQL generated ends up timing out.

We are finding ways to work around things, but are you or is anyone aware of whether the “HH:MM:SS” display format for an integer value is planned to be an out-the-box native display format in the future? (like Dates, Numbers, and Percentages, etc.) to allow these types of columns to play better in totaling?

Any insight appreciated,
Thanks.

1 Like