oracle时间与时间戳互转

时间戳转换成时间：

create or replace function sec_to_date(v_date number)
RETURN date  IS
v_srcdate number(15);
	v_tmp number(15);
 v_year varchar2(10);
 v_hour varchar2(10);
 v_min  varchar2(10);
 v_sec varchar2(10);
 v_out_date date;
begin
  v_srcdate:=v_date+8*3600;
	v_year:=to_char((v_srcdate-mod(v_srcdate,24*3600))/24/3600+to_date('19700101000000','yyyymmddhh24miss'),'yyyymmdd');
	v_tmp:=mod(v_srcdate,24*3600);
	v_hour:=to_char((v_tmp-mod(v_tmp,3600))/3600);
	if length(v_hour) =1 then
	  v_hour:='0'||v_hour;
	end if;
	v_tmp:=mod(v_tmp,3600);
	v_min:=to_char((v_tmp-mod(v_tmp,60))/60);
	if length(v_min) = 1 then
	 v_min:='0'||v_min;
	end if;
	v_tmp:=mod(v_tmp,60);
	if v_tmp <10 then
		v_sec:='0'||v_tmp;
		else
		v_sec:=to_char(v_tmp);
	end if;
	v_out_date:=to_date(v_year||v_hour||v_min||v_sec,'yyyymmddhh24miss');
	return (v_out_date);
end;

时间转换成时间戳：

create or replace function date_to_sec(src date)
RETURN number  IS
 v_cdate varchar2(24);
 v_daysec number(15);
 v_hoursec number(15);
 v_minsec number(15);
 v_secsec number(15);
 v_allsec number(15);
 begin
 v_cdate :=to_char(src,'yyyymmddhh24miss');
 v_daysec:=(trunc(src)-date'1970-01-01')*24*3600;
 v_hoursec:=to_number(substr(v_cdate,9,2))*3600;
 v_minsec:=to_number(substr(v_cdate,11,2))*60;
 v_secsec:=to_number(substr(v_cdate,13,2));
 v_allsec:=v_daysec+v_hoursec+v_minsec+v_secsec-8*3600;
  return (v_allsec);
end ;