How to convert decimal to duration-CodePudding

My data is in decimal type eg. 1.5. I need to convert into DD: HH:MM.

So in my case it should display as 1 Hr and 30 min. I'm new to sql & plsql.

CodePudding user response：

so you have a numeric value and you want to covert it to the string format days hours and minutes? -- Here are the formulas for the various parts if the variable or column is named myhours

 days = floor(myhours / 24)

 hours = floor(myhours mod 24)

 mins = 60 * ((myhour mod 24) - floor(myhour mod 24))

Then you would need to cast each of those to a varchar (with 0 prefix)

 days =  RIGHT('00'   CAST(floor(myhours / 24) AS VARCHAR(2)),2) 

 hours = RIGHT('00'   CAST(floor(myhours mod 24) AS VARCHAR(2)),2)

 mins = RIGHT('00'   CAST(60 * ((myhour mod 24) - floor(myhour mod 24)) AS VARCHAR(2)),2)

and concatenate them together.

 RIGHT('00'   CAST(floor(myhours / 24) AS VARCHAR(2)),2)   ':'  
 RIGHT('00'   CAST(floor(myhours mod 24) AS VARCHAR(2)),2)   ':'  
 RIGHT('00'   CAST(60 * ((myhour mod 24) - floor(myhour mod 24)) AS VARCHAR(2)),2)

CodePudding user response：

Try it like here: where dec_hours is the time in decimal hours and return format is dd hh24:mi:ss

SELECT 
    TO_CHAR(  TO_CHAR(TRUNC(dec_hours * 3600 / 86400),'FM00') || ' ' ||
              TO_CHAR(TRUNC(MOD((dec_hours * 3600), 86400) / 3600),'FM00') || ':' ||
              TO_CHAR(TRUNC(MOD((dec_hours * 3600), 3600) / 60), 'FM00') || ':' ||
              TO_CHAR(MOD((dec_hours * 3600), 60), 'FM00') 
          ) "DD_HH24_MI_SS"
FROM DUAL
--  
--  Result for 25.52 is
--  DD_HH24_MI_SS 
--  ---------------
--  01 01:31:12 
--
-- for 1.5      *** 00 01:30:00
-- for 734.225  *** 30 14:13:30
--

If you don't need seconds just exclude the last concatenation:

SELECT 
    TO_CHAR(  TO_CHAR(TRUNC(1.52 * 3600 / 86400),'FM00') || ' ' ||
              TO_CHAR(TRUNC(MOD((1.52 * 3600), 86400) / 3600),'FM00') || ':' ||
              TO_CHAR(TRUNC(MOD((1.52 * 3600), 3600) / 60), 'FM00') --|| ':' ||
            --  TO_CHAR(MOD((1.52 * 3600), 60), 'FM00') 
          ) "DD_HH24_MI"
FROM DUAL
--
--  R e  s u l t :
--  DD_HH24_MI 
--  ---------------
--  00 01:31

Regards...