直接减就可以了,减出单位是天数
select to_date('2004-7-1 15:42:52','yyyy-mm-dd HH24:mi:ss')-to_date('2004-6-1 13:40:52','yyyy-mm-dd HH24:mi:ss') from dual;TO_DATE('2004-7-115:42:52','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2004-6-113:40:52','YYYY-MM-DDHH24:MI:SS')
-----------------------------------------------------------------------------------------------------
30.0847222已选择 1 行。
select to_date('2004-7-1 15:42:52','yyyy-mm-dd HH24:mi:ss')-to_date('2004-6-1 13:40:52','yyyy-mm-dd HH24:mi:ss') from dual;TO_DATE('2004-7-115:42:52','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2004-6-113:40:52','YYYY-MM-DDHH24:MI:SS')
-----------------------------------------------------------------------------------------------------
30.0847222已选择 1 行。
2 RETURN VARCHAR2
3 IS
4 i NUMBER(20,14);
5 h INTEGER;
6 m INTEGER;
7 s INTEGER;
8 result VARCHAR2(20) ;
9 BEGIN
10 i:=sec/3600;
11 IF trunc(i)>0 THEN
12 h:=trunc(i);
13 i:=i-trunc(i);
14 ELSE
15 h:=0;
16 END IF;
17 IF i*60>0 THEN
18 m:=trunc(i*60);
19 i:=i*60-trunc(i*60);
20 ELSE
21 m:=0;
22 END IF;
23 IF i*60>0 THEN
24 s:=round(i*60);
25 ELSE
26 s:=0;
27 END IF;
28 result:=to_char(h)||'时'||to_char(m)||'分'||to_char(s)||'秒';
29 RETURN(Result);
30 END sec_div;
31 /Function createdSQL> select sec_div(sysdate-to_date('20040627','yyyymmdd')) from dual;SEC_DIV(SYSDATE-TO_DATE('20040
--------------------------------------------------------------------------------
0时0分5秒
2 RETURN VARCHAR2
3 IS
4 i NUMBER(20,14);
5 yy integer;
6 mm integer;
7 dd integer;
8 h INTEGER;
9 m INTEGER;
10 s INTEGER;
11 result VARCHAR2(100) ;
12 BEGIN
13 i:=sec/365;
14 if trunc(i)>0 then
15 yy:=trunc(i);
16 i:=i-trunc(i);
17 else
18 yy:=0;
19 end if;
20 if i*12>0 then
21 mm:=trunc(i*12);
22 i:=i*12-trunc(i*12);
23 else
24 mm:=0;
25 end if;
26 if i*30>0 then
27 dd:=trunc(i*30);
28 i:=i*30-trunc(i*30);
29 else
30 dd:=0;
31 end if;
32
33
34 IF i*24>0 THEN
35 h:=trunc(i*24);
36 i:=i*24-trunc(i*24);
37 ELSE
38 h:=0;
39 END IF;
40 IF i*60>0 THEN
41 m:=trunc(i*60);
42 i:=i*60-trunc(i*60);
43 ELSE
44 m:=0;
45 END IF;
46 IF i*60>0 THEN
47 s:=round(i*60);
48 ELSE
49 s:=0;
50 END IF;
51 result:=yy||'年'||mm||'月'||dd||'日'||to_char(h)||'时'||to_char(m)||'分'||to_char(s)||'秒';
52 RETURN(Result);
53 END sec_div;
54 /Function createdSQL> select sec_div(sysdate-to_date('20040627','yyyymmdd')) from dual;SEC_DIV(SYSDATE-TO_DATE('20040
--------------------------------------------------------------------------------
0年0月4日17时47分22秒
--本方法用于求两个时间的时间间隔,精确到秒
--beginTime:为开始日期 end:为结束日期
--返回值类型为varchar2
v_day float;
v_difftime varchar2(28);
v_second integer;
s_YEAR_second integer;
s_MONTH_second integer;
s_DAY_second integer;
s_HOUR_second integer;
s_MINUTE_second integer;
diff_temp float;
diff_year integer;
diff_month integer;
diff_day integer;
diff_hour integer;
diff_minute integer;
diff_second integer;
c_year varchar2(8);
c_month varchar2(4);
c_day varchar2(4);
c_hour varchar2(4);
c_minute varchar2(4);
c_second varchar2(4);
v_length integer;
begin
s_YEAR_second := 31536000; --一年的秒数
s_MONTH_second := 2592000; --一月的秒数
s_DAY_second := 86400; --一天的秒数
s_HOUR_second := 3600; --一小时的秒数
s_MINUTE_second := 60; --一分种的秒数 v_day := endTime - beginTime; --化成天
--dbms_output.put_line(v_day);
v_second := round(v_day*24*60*60); --化成秒
--dbms_output.put_line(v_difftime);
if (v_second/s_YEAR_second)>= 1 then --年
begin
diff_temp := v_second / s_YEAR_second;
diff_year := floor(diff_temp);
v_second := (v_second - diff_year * s_YEAR_second);
diff_temp := v_second / s_MONTH_second;
diff_month := floor(diff_temp);
v_second := (v_second - diff_month * s_MONTH_second);
diff_temp := v_second / s_DAY_second;
diff_day := floor(diff_temp);
v_second := (v_second - diff_day * s_DAY_second);
diff_temp := v_second / s_HOUR_second;
diff_hour := floor(diff_temp);
v_second := (v_second - diff_hour * s_HOUR_second);
diff_temp := v_second / s_MINUTE_second;
diff_minute := floor(diff_temp);
v_second := (v_second - diff_minute * s_MINUTE_second);
diff_second := v_second;
v_second := 0;
/*dbms_output.put_line(diff_year);
dbms_output.put_line(diff_month);
dbms_output.put_line(diff_day);
dbms_output.put_line(diff_hour);
dbms_output.put_line(diff_minute);
dbms_output.put_line(diff_second);*/
end;
end if;
if v_second >= s_MONTH_second and v_second < s_YEAR_second then --月
begin
diff_year := 0;
diff_temp := v_second / s_MONTH_second;
diff_month := floor(diff_temp);
v_second := (v_second - diff_month * s_MONTH_second);
diff_temp := v_second / s_DAY_second;
diff_day := floor(diff_temp);
v_second := (v_second - diff_day * s_DAY_second);
diff_temp := v_second / s_HOUR_second;
diff_hour := floor(diff_temp);
v_second := (v_second - diff_hour * s_HOUR_second);
diff_temp := v_second / s_MINUTE_second;
diff_minute := floor(diff_temp);
v_second := (v_second - diff_minute * s_MINUTE_second);
diff_second := v_second;
v_second := 0;
/*dbms_output.put_line(diff_year);
dbms_output.put_line(diff_month);
dbms_output.put_line(diff_day);
dbms_output.put_line(diff_hour);
dbms_output.put_line(diff_minute);
dbms_output.put_line(diff_second);*/
end;
end if;
if v_second >= s_DAY_second and v_second < s_MONTH_second then --日
begin
diff_year := 0;
diff_month := 0;
diff_temp := v_second / s_DAY_second;
diff_day := floor(diff_temp);
v_second := (v_second - diff_day * s_DAY_second);
diff_temp := v_second / s_HOUR_second;
diff_hour := floor(diff_temp);
v_second := (v_second - diff_hour * s_HOUR_second);
diff_temp := v_second / s_MINUTE_second;
diff_minute := floor(diff_temp);
v_second := (v_second - diff_minute * s_MINUTE_second);
diff_second := v_second;
v_second := 0;
/*dbms_output.put_line(diff_year);
dbms_output.put_line(diff_month);
dbms_output.put_line(diff_day);
dbms_output.put_line(diff_hour);
dbms_output.put_line(diff_minute);
dbms_output.put_line(diff_second);*/
end;
end if;
if v_second >= s_HOUR_second and v_second < s_DAY_second then --时
begin
diff_year := 0;
diff_month := 0;
diff_day := 0;
diff_temp := v_second / s_HOUR_second;
diff_hour := floor(diff_temp);
v_second := (v_second - diff_hour * s_HOUR_second);
diff_temp := v_second / s_MINUTE_second;
diff_minute := floor(diff_temp);
v_second := (v_second - diff_minute * s_MINUTE_second);
diff_second := v_second;
v_second := 0;
/*dbms_output.put_line(diff_year);
dbms_output.put_line(diff_month);
dbms_output.put_line(diff_day);
dbms_output.put_line(diff_hour);
dbms_output.put_line(diff_minute);
dbms_output.put_line(diff_second);*/
end;
end if;
if v_second >= s_MINUTE_second and v_second < s_HOUR_second then --分
begin
diff_year := 0;
diff_month := 0;
diff_day := 0;
diff_hour := 0;
diff_temp := v_second / s_MINUTE_second;
diff_minute := floor(diff_temp);
v_second := (v_second - diff_minute * s_MINUTE_second);
diff_second := v_second;
v_second := 0;
/*dbms_output.put_line(diff_year);
dbms_output.put_line(diff_month);
dbms_output.put_line(diff_day);
dbms_output.put_line(diff_hour);
dbms_output.put_line(diff_minute);
dbms_output.put_line(diff_second);*/
end;
end if;
if v_second <= s_MINUTE_second and v_second > 0 then --秒
begin
diff_year := 0;
diff_month := 0;
diff_day := 0;
diff_hour := 0;
diff_minute := 0;
diff_second := v_second;
v_second := 0;
/* dbms_output.put_line(diff_year);
dbms_output.put_line(diff_month);
dbms_output.put_line(diff_day);
dbms_output.put_line(diff_hour);
dbms_output.put_line(diff_minute);
dbms_output.put_line(diff_second);*/
end;
end if;
c_year := to_char(diff_year,'0');
c_month := to_char(diff_month,'0');
c_day := to_char(diff_day,'0');
c_hour := to_char(diff_hour,'0');
c_minute := to_char(diff_minute,'0');
c_second := to_char(diff_second,'0');
/*dbms_output.put_line(c_year);
dbms_output.put_line(c_month);
dbms_output.put_line(c_day);
dbms_output.put_line(c_hour);
dbms_output.put_line(c_minute);
dbms_output.put_line(c_second);*/
v_difftime := c_year||c_month||c_day||c_hour||c_minute||c_second;
v_length := length(v_difftime);
dbms_output.put_line(v_length);
return(v_difftime);
end BL_diffTimeFormat;