Oracle时间戳与时间互相转换函数_数据库

1.时间戳转时间

CREATE OR REPLACE
FUNCTION tamp_to_date (i_timestamp IN NUMBER ) return DATE IS r_date DATE;
BEGIN
SELECT
  i_timestamp/(1000*60*60*24) + to_date('1970-01-01 08:00:00','yyyy/mm/dd hh:mi:ss') into r_date from dual;
return r_date;
END tamp_to_date;

使用

INSERT INTO earlywarning_programme (CREATE_TIME )
 VALUES
  (tamp_to_date (#{CREATETIME}))

2.时间转时间戳

CREATE OR REPLACE
FUNCTION date_to_tamp (i_date IN DATE ) return NUMBER IS r_timestamp NUMBER;
BEGIN
SELECT
  ((i_date-to_date('1970-01-01','YYYY-MM-DD')) * 86400 - 8*3600)*1000 into r_timestamp from dual; 
return r_timestamp;
END date_to_tamp;

使用

date_to_tamp(create_time) as create_time,