Skip to content

    • Magic ETL

    Magic ETL

    Converting Decimels Numbers into Hours, Minutes, Seconds

    Member
    in Magic ETL

    I currently have a column that has decimel numberic values and I need to convert these values into a time format. Please note that the column containing this data is not a Date/Time column. A beastmode or and ETL transformation will work for me.

     

    Example:

    Column contains 2.25, I would like for it to display as 2:15 or 00:02:15. I will take either format.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Member
      Answer ✓

      Domo Support was able to create a beastmode to do this.

       

      Example:

      To get the Average Abandoned Time take = (SUM(`abntime`)/SUM(`abncalls`))/60.

       

      To convert mintues and fractions of a minutes this beastmode was created

       

      concat(floor(IFNULL(((SUM(`abntime`)/NULLIF(SUM(`abncalls`),0))/60),0)),'.',

      -- 1.8 = time in min --
      RIGHT(CONCAT('0', ROUND((
      (IFNULL(((SUM(`abntime`)/NULLIF(SUM(`abncalls`),0))/60),0)) * 60 -- converts it to seconds
      - FLOOR((IFNULL(((SUM(`abntime`)/NULLIF(SUM(`abncalls`),0))/60),0))) * 60)-- converts whole min to seconds (min + seconds) - (min)
      ,0)),2) -- Get rid of decimal for concat() --
      )*1

    Answers

    • Coach

      There is a function called SEC_TO_TIME() that would probably work for you.

      Are your values in minutes?  Try this : SEC_TO_TIME(2.25*60)  ->  00:02:15

      Are your values in hours?  Try this : SEC_TO_TIME(2.25*3600)  ->  02:15:00

      Aaron
      MajorDomo @ Merit Medical

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

      Thanks for the response Aaron. I have already tried sec_to_time. All get is the column changes to a text format and a value of 1.

    • Coach

      That's pretty strange @ShaneB.

      I know you mentioned your value column is a number.  Can you double check that maybe?

      How are you displaying the output of the beast mode?  Can you try a table and verify that the raw value is being calculated correctly?  Maybe send a screenshot.

      Aaron
      MajorDomo @ Merit Medical

      **Say "Thanks" by clicking the heart in the post that helped you.
      **Please mark the post that solves your problem by clicking on "Accept as Solution"
    • Member
      Answer ✓

      Domo Support was able to create a beastmode to do this.

       

      Example:

      To get the Average Abandoned Time take = (SUM(`abntime`)/SUM(`abncalls`))/60.

       

      To convert mintues and fractions of a minutes this beastmode was created

       

      concat(floor(IFNULL(((SUM(`abntime`)/NULLIF(SUM(`abncalls`),0))/60),0)),'.',

      -- 1.8 = time in min --
      RIGHT(CONCAT('0', ROUND((
      (IFNULL(((SUM(`abntime`)/NULLIF(SUM(`abncalls`),0))/60),0)) * 60 -- converts it to seconds
      - FLOOR((IFNULL(((SUM(`abntime`)/NULLIF(SUM(`abncalls`),0))/60),0))) * 60)-- converts whole min to seconds (min + seconds) - (min)
      ,0)),2) -- Get rid of decimal for concat() --
      )*1

    • Contributor

      @ShaneB - just wondering, was the chart type used in the card a table or some type of bar chart where Domo Support was able to create a beast mode that allowed you to show the average in real time, not a decimal/numeric format and minutes as a percentage? Thanks!

    • Domo Employee

      Use following formula:

      CONCAT(FLOOR((round((sum((`hours worked`))),2)*60)/60),' h ', round(MOD((round((sum((`hours worked`))),2)*60),60),2),' m')

      where `hours worked` is a column containing values like 1.77 hours.

      Here we are converting these decimal hours into minutes and then getting output in form of 'X h Y m'.


      You may change the beast mode in case your data resides in one row for each day(or time period) which would require removing sum and if you want to see ':' you may as well remove h and m and put any delimiter instead.

      Thanks. Happy DOMOing

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories