Converting Seconds Into Minutes

ShaneB
ShaneB 🟑

Hello,

I have a number column (not date) that I need to convert the number into hours, minutes, seconds (hh:mm:ss). I have now idea how.

Β 

Example:

I need 135 to display as 2:15 and not 2.25.

Β 

Β 

Comments

  • Valiant
    Valiant πŸ”΅

    You just need to use the SEC_TO_TIME() function for your seconds column.

    Β 

    Check out this article:

    https://www.w3resource.com/mysql/date-and-time-functions/mysql-sec_to_time-function.php

    Β 

    Let me know if you have any questions,

    ValiantSpur

    Β 

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • ShaneB
    ShaneB 🟑

    Thanks ValiantSpur for the article. So I have a beastmode that calculates the seconds (column A devided by column B to get this number that I needed converted into hh:mm:ss. I tried to include that in the SEC_TO_TIME() function but it did not work. Any suggestions?

  • Valiant
    Valiant πŸ”΅

    You may need to calculate the seconds column via a dataflow before attempting to use the Sec_To_TIME fucntion.

    Β 

    Also, what's the exact error message you're getting and could you provide an example of your code?

  • ShaneB
    ShaneB 🟑

    So this is what I tried sec_to_time(`abntime`/`abncalls`) and it was valid but returned nothing but 0.

  • ShaneB
    ShaneB 🟑

    So I created a new column in the ETL to divide the two columns to get my seconds, however the ETL is rounding up and dropping the decimal point. If I can find a solution to this, time_to_second function should work.

  • Have you tried converting the inputs to your calculation to decimals?Β  This may prevent the rounding you are seeing if they are currently coming in as whole numbers.

  • ShaneB
    ShaneB 🟑

    It is not rounding to a whole number just to two decimal places. This is not an issue. My main concern is breaking down a minute and and fractions of minutes to minutes and seconds.Β  Example: 2.25 minutes is 2 minutes and 15 seconds. I am stuck at converting the .25 into seconds and then adding it the 2 minutes, so my display will be 2.15 or 0:2:15.

    Β 

    Thanks for the assistance.

  • Sorry, when I was saying your rounding, I was referring to your ETL transform version and not the beast mode calculation.

    Β 

    If we go back to the beast mode formula, have you checked all of your inputs that they are strictly numbers?Β  I did some testing and the SEC_TO_TIME worked fine for all postive whole numbers and decimal numbers, but had issues with text (including negative numbers that are formatted with parenthesis).

    Β 

    I would test this with a smaller set of data to see that it works as intended and then take a look at the inputs to make sure they are all of the correct format.

  • ShaneB
    ShaneB 🟑

    Thanks for your continued assistance. My imputs are in the correct format, so I am good there. Now, when I use time_to_sec, the beastmode drops the decimal and gives me just a whole number (2.25 becomce 2 and 1.85 becomes 1). I have reached out to Domo Support for assistance.