您当前的位置: 比如 39884是一个整数，怎么把它转换成：几天几小时几分钟几秒这样的格式.如：10天10小时10分10秒。谢谢大家
------解决方案--------------------
楼上的，应该用TURNC+MOD来实现吧
------解决方案--------------------
呃，这个怎么样？
- SQL code
SELECT TRUNC (139884 / (60 * 60 * 24), 0)
|| '天'
|| MOD (TRUNC (139884 / (60 * 60), 0), 24)
|| '时'
|| MOD (TRUNC (139884 / 60, 0), 60)
|| '分'
|| MOD (TRUNC (139884 / 1, 0), 60)
|| '秒' AS TIME
FROM DUAL;
------解决方案--------------------
- SQL code
-- TRY IT ..
SQL> SELECT 10*24*60*60 + 10*60*60 + 10*60 + 10 TOTAL_SECOND_DAY,
2 11*60*60 + 12*60 + 18 TOTAL_SECOND_HOUR,
3 8*60 + 6 TOTAL_SECOND_MINUTE,
4 26 TOTAL_SECONDS
5 FROM DUAL;
TOTAL_SECOND_DAY TOTAL_SECOND_HOUR TOTAL_SECOND_MINUTE TOTAL_SECONDS
---------------- ----------------- ------------------- -------------
900610 40338 486 26
SQL> SELECT CASE
2 WHEN NUM >= 1 AND NUM < 60 -- SECOND
3 THEN
4 NUM || ' sens'
5 WHEN NUM >= 60 AND NUM < 60 * 60 -- MINUTE
6 THEN
7 TRUNC(NUM / 60) || ' mins :' || (NUM - (60 * TRUNC(NUM / 60))) ||
8 ' sens'
9 WHEN NUM >= 60 * 60 AND NUM < 24 * 60 * 60 -- HOURS
10 THEN
11 TRUNC(NUM / (60 * 60)) || ' hours :' ||
12 TRUNC((NUM - (60 * 60 * TRUNC(NUM / (60 * 60)))) / 60) ||
13 ' mins :' ||
14 (NUM - (60 * 60 * TRUNC(NUM / (60 * 60))) -
15 (60 * TRUNC((NUM - (60 * 60 * TRUNC(NUM / (60 * 60)))) / 60))) ||
16 ' sens'
17 WHEN NUM >= 24 * 60 * 60 -- DAYS
18 THEN
19 TRUNC(NUM / (24 * 60 * 60)) || ' days :' ||
20 TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60)))) /
21 (60 * 60)) || ' hours :' ||
22 TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60))) -
23 (60 * 60 *
24 TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60)))) /
25 (60 * 60)))) / 60) || 'mins :' ||
26 (NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60))) -
27 (60 * 60 *
28 TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60)))) /
29 (60 * 60))) -
30 (60 * TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60))) -
31 (60 * 60 * TRUNC((NUM - (24 * 60 * 60 *
32 TRUNC(NUM / (24 * 60 * 60)))) /
33 (60 * 60)))) / 60))) || ' sens'
34 END DAY_TIME
35 FROM (SELECT 900610 NUM
36 FROM DUAL
37 UNION ALL
38 SELECT 40338 NUM
39 FROM DUAL
40 UNION ALL
41 SELECT 486 NUM
42 FROM DUAL
43 UNION ALL
44 SELECT 26 NUM FROM DUAL);
DAY_TIME
--------------------------------------------
10 days :10 hours :10mins :10 sens
11 hours :12 mins :18 sens
8 mins :6 sens
26 sens
------解决方案--------------------
select to_timestamp(sysdate+39884/24/60/60)-to_timestamp(sysdate) from dual;