Convert Time Duration Format from HH:MM:SS to Seconds(SS)
Hello @Deepshikha, welcome to the QuickSight Community!
It will depend on how your field is set up, but the solution will require utilizing the substring and parseInt functions to break down the field, and then multiply each section to create the total minutes.
First create a calculated field. We will create 3 different substrings, set them to an integer, multiply the values (for hours, multiply by 60, then the result by 60), then we will add each value together.
timeInSeconds = ((parseInt(substring({HH:MM:SS}, 1, 2))*60)*60) + (parseInt(substring({HH:MM:SS}, 4, 2))*60) + parseInt(substring({HH:MM:SS}, 7, 2))
There isn’t a built in function to accomplish this, but this solution should give you the desired output. If you have any follow-up questions please let me know!
Thank you for the response, however it didn’t serve my purpose. I am sharing some snapshots, with reference to those kindly help me out.
So, this the first snapshot in which there is a column with name “Talking”, this the call time for a particular Caller ID. I want to fetch the total duration of calling time in the same format(HH:MM:SS). So my approach is to first convert this format in Seconds, then afterwards dividing it by 3600 to get this value in hours.
I would really appreciate if you will share your suggestions on that. I want the total call duration in format (HH:MM:SS) for particular caller ID.
Hello @Deepshikha, when you say it doesn’t serve your purpose, what exactly do you mean? I’ve used this function before it just might need some slight changes based on your data. If you hour section is only a single character, then you would change the substring function for the first section to look like this: (parseInt(substring({H:MM:SS}, 1, 1))*60)*60)
Then if you wanted to get the hours, you would divide the final value returned by the function above by 3600 to receive the new format. Any information you can provide regarding errors you have experienced or why it doesn’t serve your purpose would be helpful, but I still believe the function above can be altered slightly to provide you with the desired solution.
Hello @DylanM, I used the above function get the NULL value against it. Sharing with you the snapshot
My purpose here is to get the total duration of call time which is present in the column “Talking” for the particular “Caller ID”. Can you suggest any other way to resolve.
Hello @Deepshikha, instead of doing all of the substrings in a single calculated field, would you mind splitting it up into 3 calculated fields for each of the pieces of the talking time? Also, just to confirm, is your talking field returning a string value? I assumed it was because of how it is formatted, but I just wanted to confirm. Lastly, is there every a scenario where your hour section of the Talking time is ever greater than 0? If not, that could simplify our function and would only require 2 substrings.
I think if we break this up a bit we can figure out the error and get the function to work as expected.
Hello @DylanM, Can you please help with calculated fields which you are suggesting. It would be of great help. Just suggest me 3 calculated fields you are asking to split it up.
Hello @Deepshikha, in regards to the field I recommended above. I am suggested we run the calculations individually.
hours = parseInt(substring({HH:MM:SS}, 1, 2))
minutes = parseInt(substring({HH:MM:SS}, 4, 2))
seconds = parseInt(substring({HH:MM:SS}, 7, 2))
With these functions, you can test to make sure the index we have specified is correct and this will simply return the values from each section of the HH:MM:SS field. If these numbers match correctly to the field, then you can add aggregations.
Follow up by converting each individual field to the seconds value:
hoursInSeconds = ({hours}*60)*60
minutesInSeconds = {minutes}*60
seconds = {seconds}
Next you can ensure these values are returning as expected. Make sure the aggregations are working correctly, and this allows for easier debugging. Then, the last 3 fields can be added together to receive your final value.
totalSeconds = {hoursInSeconds} + {minutesInSeconds} + {seconds}
It just seems like there is an error occurring somewhere during this process that breaking up the fields will make it easier to find. I’m assuming one of the indexes I chose for the original function was off so it broke the returned value of the field. Let me know if you can’t resolve any errors that appear during this process, but this should lead to your desired result. Thank you!
Hello @DylanM, Thanks for the suggestion, but unfortunately the final value is showing null.
Hello @Deepshikha, can you please tell me what values you are receiving from the other calculated fields during the debugging process? When you use the minutes field and minutesInSeconds field, are values being returned?
Hello @DylanM,
No, values are not returned by using any of the calculated field.
Hello @Deepshikha, if you are not receiving any values from these calculated fields, then I would recommend filing a case with AWS Support where they can dive into the details to help you further. From my experience, this is the way that this task should be accomplished, so this must be a bug or there must be an issue within your dataset that they can help look in to. Here are the steps to open a support case.
I hope this helps!
Hello @DylanM,
This is the formula I am using for minutes.
Can we make changes in data type on dataset?
Hello @Deepshikha, what is your current datatype for talking? I assumed it was a string because of the format, am I wrong?