Display Time in HH:MM:SS from Time in Seconds in quicksight

Question & Answer
, , ,
1

I have a data field named “timeinterval” containing seconds. I’ve created a calculated field to convert these seconds into “HH:MM:SS” format. However, the calculated field is currently in string format instead of integer, making it non-aggregatable. My goal is to find the total time in “HH:MM:SS” format.

Here’s the formula for the calculated field:

concat(

ifelse(timeinterval / 3600 < 10,concat(‘0’,tostring(decimalToInt(floor(timeinterval / 3600) ))),tostring(decimalToInt(floor(timeinterval / 3600))))

,‘:’,

ifelse((timeinterval % 3600)/60 < 10,concat(‘0’, tostring(decimalToInt(floor((timeinterval % 3600)/60)))),tostring(decimalToInt(floor((timeinterval % 3600)/60))))

,‘:’,

ifelse(timeinterval % 60 < 10,concat(‘0’,tostring(decimalToInt(floor(timeinterval % 60)))),tostring(decimalToInt(floor(timeinterval % 60))))

)

2

Hey @teja1 ,

sadly I don’t think what you plan is possible. There is no appropriate datatype and that concat with ‘,’ disqualifies it as an int. You best bet would be to calculate with the seconds from the timeinterval column and change that result into your ‘HH:mm:SS’ format.

BR
Robert

1 Like
3

Hi @robert.eibers, Thank you for your time. I implemented the suggestions you provided, and while it is working fine, the results are not exactly what I was hoping for.

4

Hello @teja1 and @robert.eibers !

@teja1 are you still running into this problem or is @robert.eibers suggestion serving as a good workaround? Can you break down more what you mean by the solution is not what you were hoping for?

5

Hi @duncan, thank you for reaching out. I’ve managed to resolve the issue and figured out how to make the time (HH:MM:SS) aggregatable. I appreciate your concern!

1 Like
6

Hey @teja1, thank you for getting back to me!

Could you share how you were able to do that to help the community?

7

Hello @duncan, kindly check out the following link:

2 Likes
8

@teja1 Excellent, thank you!

1 Like