Take sum of seconds and display as #hrs, #minutes

micheleb

I have totaled the number of seconds for several cards and need to take the sum and show it as #Hours, #minutes.

I'm struggling to find a way to do this in beast mode. This is not converting a time value but altering the sum.

For example, if I have a total of 8,274 seconds

First, you find the number of whole hours

8,274 seconds = 8,274 seconds ÷ 3,600

8,274 seconds = 2.29833 hours

# of full hours = 2

Then Find the number of whole minutes

minutes = .29833 hours × 60 minutes

minutes = 17.9 minutes

# of full minutes = 17

So 8,274 seconds is equivalent to 2 hours and 17 minutes

How do I split the decimal value 2.17 so that I can multiply 0.29833 X 60

micheleb

I think the problem lies in that I am using page filter so the aggregation has to happen after the filtering happens. It's not individual records which is why the sec to time doesn't work.

For example, users need to be able to use the page filters to select the specific cards they are interested in.

In this case, after filter - the total minutes for the filtered cards is equal to 102 minutes which is the equivalent of 1.7 hours. What I haven't been able to do is extract the .7 from 1.7 on order to mutiply it by 60 to get 42 minutes. 1.7 hours is equal to 1 hr and 42 minutes

This is what happens when you use sec to time. perhaps its rounding up but 2 hr and o minutes is not correct. It should be 1hr and 42 minutes

Let me try the floor - I had not thought - if I could pull out the .7 and the 1 seperately.

GrantSmith

00:02:00 is 2 minutes, not 2 hours. Have you tried wrapping your seconds in a SUM function before passing to SEC_TO_TIME?

SEC_TO_TIME(SUM(`total_seconds`))

GrantSmith

Hi @micheleb

you can use SEC_TO_TIME to take the number of seconds and format it with hours:minutes:seconds. I’ve done another write up of custom timespan difference here: https://dojo.domo.com/discussion/52682/domo-ideas-conference-beast-modes-time-difference-formatting#latest

micheleb

Thanks for your help ----

SEC TO TIME is converting the number of seconds (37,900) into a time 01:54:49 am.

I have a whole number which is 37,900 seconds - this is the same as 10 hrs, 31 minutes and 49 seconds.

I don't need a time value - I actually need a conversion of the # of seconds to what it would be in hours and minutes. Like converting cups to ounces or pounds to kg. I am summing the number of hours it takes to watch several video courses.

This is an online calculator that does the conversion https://www.inchcalculator.com/seconds-to-time-calculator/ I just need to figure out how to mimic those same calculations in beast mode.

I can take the number of seconds and divide by 3600 which gives me the number of whole hours 10.527777778. What I can't figure out is how to extract the remainder .52777778 from that value since it is a number. The MOD formula does something different.

MarkSnodgrass

@micheleb are you applying some additional formatting or something? When I put this in the beast mode editor:

SEC_TO_TIME(37900)

The card resolves to

That seems like what you are going for.

GrantSmith

Hours: - Divide the total seconds by the number of seconds in an hour (3600) and drop the remainder (FLOOR)

FLOOR(`total_seconds` / 3600)

Minutes - Get the remainder seconds after converting to hours then divide that by 60 to convert to minutes. Floor to drop the remainder

FLOOR(MOD(`total_seconds`, 3600) / 60)

Seconds: - Divide by 60 and take the remainder to get the number of seconds left over.

MOD(`total_seconds`, 60)

micheleb

I think the problem lies in that I am using page filter so the aggregation has to happen after the filtering happens. It's not individual records which is why the sec to time doesn't work.

For example, users need to be able to use the page filters to select the specific cards they are interested in.

In this case, after filter - the total minutes for the filtered cards is equal to 102 minutes which is the equivalent of 1.7 hours. What I haven't been able to do is extract the .7 from 1.7 on order to mutiply it by 60 to get 42 minutes. 1.7 hours is equal to 1 hr and 42 minutes

This is what happens when you use sec to time. perhaps its rounding up but 2 hr and o minutes is not correct. It should be 1hr and 42 minutes

Let me try the floor - I had not thought - if I could pull out the .7 and the 1 seperately.

GrantSmith

00:02:00 is 2 minutes, not 2 hours. Have you tried wrapping your seconds in a SUM function before passing to SEC_TO_TIME?

SEC_TO_TIME(SUM(`total_seconds`))

micheleb

Ahh - thanks you - wrapping the total seconds in a sum seemed to do the trick.