Converting Decimels Numbers into Hours, Minutes, Seconds

Options
ShaneB
ShaneB Contributor
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.

Best Answer

  • ShaneB
    ShaneB Contributor
    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

  • AS
    AS 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"
  • ShaneB
    ShaneB Contributor

    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.

  • AS
    AS 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"
  • ShaneB
    ShaneB Contributor
    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

  • John-Peddle
    John-Peddle 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!

  • LoveBindal
    LoveBindal 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

Categories