select floor(zzsj- (qssj-10.322323))||'天'||
floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24)
||'时'||
floor(((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24 -floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24))*60
)||'分'||
floor((((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24 -floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24))*60 - floor(((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24 -floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24))*60
)) * 60)||'秒' from dual;例子:
zzsj:sysdate
qssj:sysdate - 10.10.32232311:26:18 jlanzpa817>select floor(sysdate - (sysdate-10.322323))||'天'||
11:28:53 2 floor((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24)
11:28:53 3 ||'时'||
11:28:53 4 floor(((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24 -floo
r((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24))*60
11:28:53 5 )||'分'||
11:28:53 6 floor((((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24 -flo
or((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24))*60 - floor(((sysdate
- (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24 -floor((sysdate - (sysdate-10.32232
3) - floor(sysdate - (sysdate-10.322323)))*24))*60
11:28:54 7 )) * 60)||'秒' from dual;FLOOR(SYSDATE-(SYSDATE-10.322323))||'天'||FLOOR((SYSDATE-(SYSDATE-10.322323)-FLO
--------------------------------------------------------------------------------
10天7时44分9秒已用时间: 00: 00: 00.10
floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24)
||'时'||
floor(((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24 -floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24))*60
)||'分'||
floor((((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24 -floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24))*60 - floor(((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24 -floor((zzsj- (qssj-10.322323) - floor(zzsj- (qssj-10.322323)))*24))*60
)) * 60)||'秒' from dual;例子:
zzsj:sysdate
qssj:sysdate - 10.10.32232311:26:18 jlanzpa817>select floor(sysdate - (sysdate-10.322323))||'天'||
11:28:53 2 floor((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24)
11:28:53 3 ||'时'||
11:28:53 4 floor(((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24 -floo
r((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24))*60
11:28:53 5 )||'分'||
11:28:53 6 floor((((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24 -flo
or((sysdate - (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24))*60 - floor(((sysdate
- (sysdate-10.322323) - floor(sysdate - (sysdate-10.322323)))*24 -floor((sysdate - (sysdate-10.32232
3) - floor(sysdate - (sysdate-10.322323)))*24))*60
11:28:54 7 )) * 60)||'秒' from dual;FLOOR(SYSDATE-(SYSDATE-10.322323))||'天'||FLOOR((SYSDATE-(SYSDATE-10.322323)-FLO
--------------------------------------------------------------------------------
10天7时44分9秒已用时间: 00: 00: 00.10
//注意
*24是时差,*24*60分差,*24*60*60秒差
只是举个例子验证一下。
(startDate date,
endDate date
);insert into tstdate values(sysdate - 10.7,sysdate);
commit;Select
To_char( startDate, 'dd/mm/yyyy hh24:mi:ss' ) startDate,
To_char( EndDate, 'dd/mm/yyyy hh24:mi:ss' ) EndDate,
Trunc( days ) || '天' ||
To_Char(Trunc(Mod(hrs,24)),'fm00') || '小时' ||
To_Char(Trunc(Mod(mns,60)),'fm00') || '分' ||
To_Char(Trunc(Mod( sec ,60)),'fm00')||'秒' Elaps
From (select startdate,
enddate,
cast( enddate-startdate as number(38,20) ) days,
cast( (enddate-startdate)*24 as number(38,20) ) hrs,
cast( (enddate-startdate)*24*60 as number(38,20) ) mns,
cast( (enddate-startdate)*24*60*60 as number(38,20) ) sec
from tstdate );15:55:26 jlanzpa817>/20/12/2002 23:04:46 31/12/2002 15:52:46
10天16小时48分00秒已用时间: 00: 00: 00.10
select * from table where id in (select decode(sign((zzrq-qsrq)*24-x),1,id,null) from table)