Take sum of seconds and display as #hrs, #minutes
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
Best Answers
-
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.
0 -
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`))
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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.
0 -
@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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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.
0 -
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`))
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Ahh - thanks you - wrapping the total seconds in a sum seemed to do the trick.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive