Forum Stats

  • 3,826,359 Users
  • 2,260,635 Discussions
  • 7,896,916 Comments

Discussions

Convert number to time

Bjornlaat
Bjornlaat Member Posts: 18 Green Ribbon
edited Jun 22, 2022 12:00PM in SQL & PL/SQL

Good morning, 


I need some help converting a number to time. 


For example, the value that comes out of SQL is: 7.5 

This number needs to be converted to time: 07:30 how can I solve this? 


another example is : 13.75 and this number should be converted to 13:45


Thanks in advance for the help :)

Best Answer

«1

Answers

  • cormaco
    cormaco Member Posts: 1,942 Silver Crown
    edited Jun 22, 2022 12:32PM

    Here is one way:

    with hours(h) as (select * from table(sys.odcinumberlist(7.5,13.75)))
    select h,(to_char(trunc(sysdate) + h/24,'HH24:MI:SS')) hhmm from hours
    
             H HHMM    
    ---------- --------
           7.5 07:30:00
         13.75 13:45:00
    

    EDIT: You can also use any date literal like DATE '2022-01-01' instead of trunc(sysdate)

    SmithJohn45
  • Stax
    Stax Member Posts: 41 Red Ribbon
    SQL> ed
    Wrote file afiedt.buf
    
    
      1  with t as (
      2  select 7.5 n from dual union all
      3  select 13.75  from dual union all
      4  select 20.333  from dual union all
      5  select 2.00  from dual union all
      6  select 0.25  from dual union all
      7  select 25.3 from dual
      8  )
      9* select n,trunc(n) h,round(60*mod(n,1)) mi from t
    SQL> /
    
    
             N          H         MI
    ---------- ---------- ----------
           7.5          7         30
         13.75         13         45
        20.333         20         20
             2          2          0
           .25          0         15
          25.3         25         18
    
    
    6 rows selected.
    
    
    
    SmithJohn45
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond

    @cormaco: Here is one way

    It will only work if number < 24. Otherwise:

    with hours(h) as (select * from table(sys.odcinumberlist(7.5,13.75,33.25)))
    select h,(to_char(trunc(sysdate) + h/24,'HH24:MI:SS')) hhmm from hours
    /
    
             H HHMM
    ---------- --------
           7.5 07:30:00
         13.75 13:45:00
         33.25 09:15:00 -- wrong
    
    SQL>
    

    SY.

  • User_3ABCE
    User_3ABCE Member Posts: 14 Red Ribbon
    Answer ✓
    select to_char(time'0:0:0'+numtodsinterval(7.5,'hour'),'hh24:mi:ss') tm from dual;
    
    TM      
    --------
    07:30:00
    
  • cormaco
    cormaco Member Posts: 1,942 Silver Crown
    time'0:0:0'
    

    The time literal is an undocumented feature, but date '1-1-1' works as well.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond

    First of all, type TIME is undocumented. Secondly, same as solution from cormaco it will not work if number >= 24:

    SQL> select to_char(time'0:0:0'+numtodsinterval(39.25,'hour'),'hh24:mi:ss') tm from dual;
    
    TM
    --------
    15:15:00
    
    SQL>
    

    SY.

  • Stax
    Stax Member Posts: 41 Red Ribbon
    SQL> select numtodsinterval(39.25,'hour') from dual;
    
    
    NUMTODSINTERVAL(39.25,'HOUR')
    ---------------------------------------------------------------------------
    +000000001 15:15:00.000000000
    
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond

    @Stax: This displays it as days and hours while OP, if I got it right, wants total hours: 39:15.

    SY.

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    Here is one way. The formula uses a trivial algebraic simplification of the following approach: keep the integer part as the number of hours; multiply the fractional part by 0.6 (60/100). Then format the result using colon as the decimal point, to get the result in the string format hh:mi.

    with
      inputs (decimal_hours) as (
        select   7.5  from dual union all
        select   4.25 from dual union all
        select 140    from dual union all
        select   0.05 from dual union all
        select   2.47 from dual
      )
    select decimal_hours,
           to_char(0.6 * decimal_hours + 0.4 * trunc(decimal_hours), 'fm99999990d00',
                   'nls_numeric_characters=:.') as hh_mi
    from   inputs;
    
    DECIMAL_HOURS   HH_MI       
    -------------   ------------
              7.5   7:30        
             4.25   4:15        
              140   140:00      
              .05   0:03        
             2.47   2:28 
    
    

    Note the rounding applied to the last sample input. 0.47 hours is not a whole number in minutes, it's 28.2 minutes. If you don't want that result shown as 2;28 (2 hours and 28 minutes), since that loses precision, indicate what else you want as output. Perhaps 2:28.2 or 2:28:24 (in hh:mi:ss format) - understanding that even the seconds may not always be a whole number, so there may still be some rounding even then.

  • Bjornlaat
    Bjornlaat Member Posts: 18 Green Ribbon

    Thank you all for your help! We have solved the problem through your help.