Convert number to time

Question

How to convert number 1.33408564814814 to time 32:01:05?

Answer 1

Is this post what you are looking for?

Converting a number into an HH:MM:SS time format - Oracle

Answer 2

SELECT
-- EXTRACT(day FROM numtodsinterval(1.33408564814814 , 'DAY')) Days,
EXTRACT(day FROM numtodsinterval(1.33408564814814 , 'DAY')) * 24 + EXTRACT(hour FROM numtodsinterval(1.33408564814814 , 'DAY')) TotalHours,
-- EXTRACT(hour FROM numtodsinterval(1.33408564814814 , 'DAY')) Hours,
EXTRACT(minute FROM numtodsinterval(1.33408564814814 , 'DAY')) Minutes,
EXTRACT(second FROM numtodsinterval(1.33408564814814 , 'DAY')) Seconds
FROM dual;

Answer 3

If you actually want the result as a character string, you could use a function like this:

set serveroutput on format wrapped;

declare
function days_to_time (p_days number) return varchar2
is
  d number := p_days;
  h integer;
  m integer;
  s integer;
begin
  h := trunc(d*24);
  d := d - h/24;
  m := trunc(d*24*60);
  d := d - m/24/60;
  s := round(d*24*60*60);
  return(h||':'||to_char(m,'FM00')||':'||TO_CHAR(s,'FM00'));
end;
begin
  dbms_output.put_line(days_to_time(1.33408564814814));
end;
/

anonymous block completed
32:01:05

Answer 4

The original number is an interval expressed as a number of days. The following is a bit ugly but might help show what's going on:

SELECT days,
       hours,
       minutes,
       seconds,
       TO_CHAR(days, 'FM9') || ' ' ||
         TO_CHAR(day_hours, 'FM09') || ':' ||
         TO_CHAR(minutes, 'FM09') || ':' ||
         TO_CHAR(seconds, 'FM09') AS interval_string,
       TO_DSINTERVAL(TO_CHAR(days, 'FM9') || ' ' ||
                       TO_CHAR(day_hours, 'FM09') || ':' ||
                       TO_CHAR(minutes, 'FM09') || ':' ||
                       TO_CHAR(seconds, 'FM09')) actual_interval
 FROM (SELECT float_val,
              days, hours, minutes,
              hours - (days * 24) AS day_hours,
              ROUND((((decimal_hours - hours) * 60) - trunc((decimal_hours - hours) * 60)) * 60) AS seconds
      FROM (SELECT float_val,
                   trunc(hours / 24) AS days,
                   decimal_hours,
                   hours,
                   (decimal_hours - hours) * 60 AS decimal_minutes,
                   trunc((decimal_hours - hours) * 60) AS minutes
              FROM (SELECT float_val,
                           float_val * 24 AS decimal_hours,
                           TRUNC(float_val* 24) AS hours
                      FROM (SELECT 1.33408564814814 AS float_val FROM dual))));

Share and enjoy.