oracle 将分钟转换为 HH24:MI 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11931574/
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 minutes to HH24:MI format
提问by ZEE
Can you please help in converting minutes to the format of ('HH24:MI').
您能帮忙将分钟转换为 ('HH24:MI') 的格式吗？
I am getting the result in integer which is the minutes. So, how to convert them?
我得到的结果是整数，也就是分钟。那么，如何转换它们呢？
Thanks
谢谢
回答by René Nyffenegger
Assuming, you want to convert 492 minutes:
假设您要转换 492 分钟：
select to_char(trunc(sysdate) + 492/24/60, 'hh24:mi') from dual;
If you want a function:
如果你想要一个函数：
create or replace
function tq84_convert_minutes_hh24mm (p_minutes in number)
return varchar2 is
begin
return to_char (trunc(sysdate) + p_minutes / 24/60, 'hh24:mi');
end tq84_convert_minutes_hh24mm;
/
Later ...
之后 ...
begin
dbms_output.put_line (tq84_convert_minutes_hh24mm(492));
end;
/
回答by Bob Jarvis - Reinstate Monica
Another way:
其它的办法：
WITH c AS
(SELECT 492 AS MINUTES FROM DUAL)
SELECT TRIM(TO_CHAR(TRUNC(MINUTES / 60), '09')) || ':' ||
TRIM(TO_CHAR(TRUNC(MOD(ABS(MINUTES), 60)), '09')) AS HHMM
FROM C
This will have issues if the time exceeds 1440 minutes (24 hours) but it gives you something to start with.
如果时间超过 1440 分钟（24 小时），这将有问题，但它为您提供了一些开始。
Share and enjoy.
分享和享受。
回答by Jean Paulo
This can save you, but I had problems with time greater than 1440 minutes.
这可以节省您的时间，但是我遇到了时间超过 1440 分钟的问题。
select to_char(trunc(sysdate) + [MINUTES]/24/60, 'hh24:mi') from dual;
So I did a function that verifies if minute is greater or equal 1440:
所以我做了一个函数来验证分钟是否大于或等于 1440：
IF (QT_MINUTES_P IS NOT NULL) THEN
IF (QT_MINUTES_P >= 1440) THEN
SELECT ROUND(QT_MINUTES_P/ 60) ||':'|| MOD(QT_MINUTES_P, 60)
INTO DS_RESULT
FROM DUAL;
ELSE
SELECT TO_CHAR(TRUNC(SYSDATE) + (QT_MINUTES_P)/24/60, 'hh24:mi')
INTO DS_RESULT
FROM DUAL;
END IF;
END IF;
