How to create a date out that shows days, hours, minutes, seconds

Hello! I am very new to Quicksight and trying to figure out the basics! Within the Visualize section, I have a “call_start_time” field and a “call_end_time” field and I am trying to create a calculated field called the “total_call_duration” using those two fields as the start and end reference points.

I am using the following function and definition, which is functional, but obviously only shows hours for the output:

  1. dateDiff({call_start_time}, {call_end_time}, “HH”)

Is there a way to add to this definition within the same function so that the output includes DD:HH:MI:SS??

If not, how would I go about creating a field that produces this output in this format?

Thank you for your time!

Hi @jfant001 - There is NO out of the box solution for this problem. You have create a calculate fields for finding days, hours, minute, seconds ( hours, minutes and seconds needs to put some logic so that you can do minus) and concatenated the fields and populate the data as per your requirements.

I will also try from my side and update you.

Regards - San

1 Like

Take a look at this article:

1 Like

Okay, thank you for the response, @Sanjeeb2022 Sanjeeb. I will keep working with it to try and figure out how to create the output that I need.

Most of our event data shows “hours” and “minutes” for “start” and “end” times so that is mainly what I need for the output, but I wanted to ask about adding “days” and “seconds” in case there was a simple addition that could be made that I had not figured out yet.

Thank you again!

1 Like

Thank you for the response, @David_Wong David. We have call start and end times in our data fields that are displayed with hours, minutes, and seconds so I just need to create a calculated field that has an output that basically shows the call duration in hours and minutes. I appreciate the information and I will see if I can use any of your definition to help me create what I need.

Just for more clarity of what I need, if in field “call_start_time” shows “04/16/2023 09:30” and in field “call_end_time” shows 04/16/2023 14:38, I need the calculated field of “call_duration” to show 5:08 for the call duration.

Thank you again!

1 Like

HI @jfant001 ,

Thanks for your query. Before going to create your calculated field as DD:HH:MI:SS you need to first create your date difference that is Call Start Time and Call End Time duration as sec.

Step -1

dateDiff({Call Start Time},{Call End Time}, ‘SS’)

Step - 2

Use this below formula

The yellow highlighted area is the field name of your date duration between start time & end time.

Note - You can not plot this data on KPI visual type.

If you want to Create DD:HH:MM:SS format for line level then just go to the Demo Central and see the detailed analysis it is very easy to understanding all the calculation.

DemoCentral (learnquicksight.online)

If you feel any difficulties please revert back .

Thanks & Regards
Biswajit Dash

2 Likes