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

  • micheleb
    micheleb Member
    edited January 2022 Answer ✓

    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
    GrantSmith Coach
    Answer ✓

    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!**

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!**
  • micheleb
    micheleb Member
    edited January 2022

    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.

  • @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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    edited January 2022

    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!**
  • micheleb
    micheleb Member
    edited January 2022 Answer ✓

    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
    GrantSmith Coach
    Answer ✓

    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!**
  • Ahh - thanks you - wrapping the total seconds in a sum seemed to do the trick.