本文参考自: 原文地址
1:整数到时间的转换:
create or replace function inttotime(in_number NUMBER) return date is
begin
return(TO_DATE('19700101','yyyymmdd') + in_number/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24);
end inttotime;
使用:
select inttotime(1420795820) from dual;
结果:
2015/1/9 17:30:20
2:时间到整数的转换:
create or replace function timetoint(in_date IN DATE) return number is
begin
return( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600);
end timetoint;
使用:
select timetoint(to_date('2015/1/9 17:30:20','yyyy/mm/dd hh24:mi:ss')) from dual;
结果:
1420795820