select sysdate - to_date('2012-03-29 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual 得到秒,具体的自己换算!
给你个例子:SELECT EXTRACT(DAY FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND ) || ' days ' || EXTRACT(HOUR FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND ) || ' hours' || EXTRACT(MINUTE FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND ) || ' minute' || EXTRACT(SECOND FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND ) || ' second' "Interval" FROM DUAL;
select trunc(t/86400) 天, trunc((mod(t,86400))/3600) 时, trunc(mod(mod(t,86400),3600)/60) 分, mod(mod(mod(t,86400),3600),60) 秒 from (select (sysdate-to_date('2012-03-20 12:12:12','yyyy-mm-dd hh24:mi:ss'))*86400 t from dual) 天 时 分 秒 ------------------------------------------- 1 9 21 50 10
或者调用存储过程 CREATE OR REPLACE Function timeinterval (d_begindate Date,d_enddate Date) --计算两个日期之间相差的天数、小时数、分钟数、秒数 Return Varchar2 Is s_timeinterval Varchar2(40);n_timeinterval Number;n_day Number;n_hour Number;n_minute Number;n_second Number;n_timesurplus Number; Begin If d_begindate Is Null Or d_enddate Is Null Then s_timeinterval:=Null; Else Select (d_enddate-d_begindate)*24*60*60 Into n_timeinterval From dual; n_day:=floor(n_timeinterval/24/60/60); n_timesurplus:=Mod(n_timeinterval,24*60*60); n_hour:=floor(n_timesurplus/60/60); n_timesurplus:=Mod(n_timesurplus,60*60); n_minute:=floor(n_timesurplus/60); n_timesurplus:=Mod(n_timesurplus,60); n_second:=floor(n_timesurplus); s_timeinterval:=n_day||'天'||n_hour||'小时'||n_minute||'分'||n_second||'秒'; End If; Return s_timeinterval; End;
select sysdate - to_date('2012-03-29 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual
得到秒,具体的自己换算!
EXTRACT(DAY FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
|| ' days '
|| EXTRACT(HOUR FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
|| ' hours'
|| EXTRACT(MINUTE FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
|| ' minute'
|| EXTRACT(SECOND FROM (sysdate-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
|| ' second' "Interval"
FROM DUAL;
trunc((mod(t,86400))/3600) 时,
trunc(mod(mod(t,86400),3600)/60) 分,
mod(mod(mod(t,86400),3600),60) 秒
from
(select (sysdate-to_date('2012-03-20 12:12:12','yyyy-mm-dd hh24:mi:ss'))*86400 t from dual) 天 时 分 秒
-------------------------------------------
1 9 21 50 10
CREATE OR REPLACE Function timeinterval (d_begindate Date,d_enddate Date) --计算两个日期之间相差的天数、小时数、分钟数、秒数
Return Varchar2
Is
s_timeinterval Varchar2(40);n_timeinterval Number;n_day Number;n_hour Number;n_minute Number;n_second Number;n_timesurplus Number;
Begin
If d_begindate Is Null Or d_enddate Is Null Then
s_timeinterval:=Null;
Else
Select (d_enddate-d_begindate)*24*60*60 Into n_timeinterval From dual;
n_day:=floor(n_timeinterval/24/60/60);
n_timesurplus:=Mod(n_timeinterval,24*60*60);
n_hour:=floor(n_timesurplus/60/60);
n_timesurplus:=Mod(n_timesurplus,60*60);
n_minute:=floor(n_timesurplus/60);
n_timesurplus:=Mod(n_timesurplus,60);
n_second:=floor(n_timesurplus);
s_timeinterval:=n_day||'天'||n_hour||'小时'||n_minute||'分'||n_second||'秒';
End If;
Return s_timeinterval;
End;