Working with hh:mm:ss values

bstacy

Hello everyone,

I am relatively new to Domo but have built a few recursive data set, dashboards, and cards already and really enjoy the product.

I am running into an issue however when trying to figure out how to best work with duration data that is formatted from the source system as hh:mm:ss.

I have created beast mode fields to convert each of these values into seconds using substrings and those work just fine. However, when displaying the data in a card, for example to show a total over given period of time, I would like the data to be converted back to hh:mm:ss format.

What is the best way to accomplish this?

MarkSnodgrass

I took your sample times and was able to add them up using the following in Magic ETL:

Your group by would likely look different than mine, likely some sort of event id or something.

I used split_part to break out the individual time elements and get a total number of seconds and then use the group by to sum it up. The next formula tile breaks it back out to the individual time elements and the concatenate them together. I use Floor to get the highest whole number and then convert it to an integer so that it is a whole number.

MarkSnodgrass

I would use the UNIX_TIMESTAMP function for both of your datetimes, which will convert them into numbers. You can then easily subtract the two to get the number of seconds between the two and then use the SEC_TO_TIME function to convert it back to a time format.

UNIX_TIMESTAMPIf called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC). The return value is an integer if no argument is given or the argument does not include a fractional seconds part, or DECIMAL if an argument is given that includes a fractional seconds part.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

SEC_TO_TIMEConverts seconds into hours, minutes, and seconds.

For example, 3489 would be converted into 00:58:09.

SEC_TO_TIME('Seconds')

bstacy

To be clear, I'm not dealing with a date time field but rather fields which contain a duration of specific events. For example, the total amout a user may have been logged in in hh:mm:ss for a given date. That said, I think that the SEC_TO_TIME function may be what I am looking for. I will try this and confirm whether or not this does the trick. Thank you.

bstacy

It doesn't appear that SEC_TO_TIME is going to work for what I am attempting to do. As per the documentation, it is limited to 24 hours, and when a value is greater than 24 hours, it will overflow and loop back to 0 hours.

What I am attempting to accomplish is that if Employee A's logged in time was 6:43:15, 5:15:45, 8:45:11, 7:52:10, 8:20:20 for each of the five days in a given work week, I would like to be able to show these total logged in time for this period as 36:56:41.

There are many more statistics that i need to handle in this manner. I am simply using logged in as a simple example. For larger date ranges these values will regularly exceed 24 hours which as far as I can tell excludes SEC_TO_TIME as a possible solution.

MarkSnodgrass

I took your sample times and was able to add them up using the following in Magic ETL:

Your group by would likely look different than mine, likely some sort of event id or something.

I used split_part to break out the individual time elements and get a total number of seconds and then use the group by to sum it up. The next formula tile breaks it back out to the individual time elements and the concatenate them together. I use Floor to get the highest whole number and then convert it to an integer so that it is a whole number.

bstacy

Wow…thank you for putting that amount of time into this. This is extremely helpful!