To convert time in milliseconds to MM:SS format

[tsh1089]

Dear Community,

I am stuck at a step in trying to convert the time field value to show as MM:SS as the final output on the visual.

In the attached image:

1. You can see that I am able to convert the millisecond to MM:SS format and display it in the table using the calculated field as below:
   concat(
   ifelse(floor({time} / 60000) < 10, concat(‘0’, tostring(floor({time} / 60000))), tostring(floor({time} / 60000))),
   ‘:’,
   ifelse((floor({time} % 60000) / 1000) < 10, concat(‘0’, tostring(floor((floor({time} % 60000) / 1000)))), tostring(floor((floor({time} % 60000) / 1000))))
   )
2. A is Sum of time field in seconds (using sum({time})/1000 )
3. B is Average of time in seconds (using avg({time})/1000)
4. In C I am trying to display either A or B in the format MM:SS using the calculated field as below:
   concat(
   toString(floor(B / 60)),
   ‘:’,
   ifelse(
   B - (floor(B / 60) * 60) < 10,
   concat(‘0’, toString(B - (floor(B / 60) * 60))),
   toString(B - (floor(B / 60) * 60))
   )
   )

I am aware that the field C is string and I tried to use “parseDecimal” or even “parseInt” to change the format in D but unfortunately none of the solution in either C or D fields gives me the desired end result. Instead in C I get We can't parse this SQL syntax. If you are using custom SQL, verify the syntax and try again. Otherwise, contact support. and in D I get NO DATA, to be able to view the final value of averaged time in the format MM:SS.

I am hoping I can get some assistance here and would greatly appreciate.

I have also tried the community solutions given in:

Seconds (string) to "HH:mm:ss" time convertion

Hello There, Hope you can guide me here! I have a field called “Avg Handled Time”. it’s Data type is “string”, and the value in seconds (example: 650) How can I present the “seconds” in the “HH:mm:ss” format? Any help would be greatly appreciated

Convert calculated field from String to Integert type

Hi @dipanshugarg - Not sure, but in documentation it is also not there. Hi @DylanM - any clue on this. Regards - Sanjeeb

But they as well are not helpful to my use case.

[Deep]

Hello Tushar,

Welcome to the QuickSight Community.

I am not sure if this will help but have a look here -

https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Duration-Time-Format-HH-MM-SS-

Thank you.

Cheers,
Deep

[tsh1089]

Hello Deep,

Many thanks for sharing the solution so quickly. I am going to test it out on my use case today and will update before the EoD.

Thanks and regards,
Tushar

[tsh1089]

Hello Deep,

I tried using the provided solution and it works fine to show each data in the field of the dataset converted to the desired time format I want. See ref screenshot below:

This works without any issue.

Now what I am trying to achieve further is:

in screenshot - averaging the time in column 2 (Time in seconds) and displaying it as a visual which here gives 273.98. Further converting it to integer displays 273. Now this 273 is in seconds and I want to view it in either HH:MM:SS or MM:SS. I tried using the formula you gave and I get the error as can’t parse SQL syntax.

This is where I need the assistance, if you can help with or guide me on how can I view this final averaged time value (in seconds) in MM:SS or HH:MM:SS format. Is this possible to do in quicksight or is it some sort of limitation?

I hope my question is clear now and I look forward to your repsonse.

[Deep]

Hello Tushar,

See this post if it helps -

Convert time duration to HH:MM:SS

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(({Averag…

Display Time in Seconds to HH:MM:SS

Hello everyone, I wanted to share a solution I found for converting seconds to HH:MM:SS in Amazon QuickSight. When using the formula provided by the QuickSight community in a calculated field, the result is in string format, which isn’t suitable for aggregation. After searching for a solution and not finding any source, I managed to figure it out. Here’s a step-by-step guide: Step 1: Create a calculated field (e.g., “SumTimeInterval”) and use the formula: sum(seconds) Step 2: Create anoth…

Hope it works for you.

Cheers,
Deep

[tsh1089]

Hi Deep,

Thank you sharing the links. I checked them but the issue still exists. As I stated in my reply earlier today that I want that averaged value “273 seconds” to show me as 1 value in visual in either HH:MM:SS or MM:SS format of time.

Let me share the question again below using the attached screenshot as reference:

The provided solution works for 7 count of events as shown in screenshot row by row and displays the converted time in desired format.

whereas if I go ahead with creating a new calculated field “sum(time)” where time contains values (47,43,43,41,39,39,39) respectively for corresponding 7 entries in row, I get an output of 291 seconds. Using the solution provided by you, or the solutions mentioned by others in the community with my input field now as field “sum(time)” - I get the parse error.

Look forward to your response. Thanks.

[tsh1089]

Hi Community and @Deep

Is it possible to have any further guidance or suggestion with this query?

Best Regards,
Tushar

[tsh1089]

Hi,

I hope community is reading and can share if it is possible to get some assistance on this thread or not?

Regards,
Tushar

[Brett]

Hi @tsh1089,
It’s been awhile since last communication on this thread. Checking in to see if you were still having issues with your original topic or if you were able to find a work around?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[Brett]

Hi @tsh1089,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information that may be needed.

Thank you!