oracle 将数字转换为时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3978656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert number to time
提问by DejanL
How to convert number 1.33408564814814 to time 32:01:05?
如何将数字 1.33408564814814 转换为时间 32:01:05？
回答by Tony Andrews
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
回答by vc 74
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;
回答by Bob Jarvis - Reinstate Monica
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.
分享和享受。
