日期转UTC
select (to_date('2013-04-09 14:02:15','yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01','yyyy-mm-dd'))*86400 from dual;
UTC转日期
select to_char(to_date(19700101,'yyyymmdd') + 1365516134.9/86400,'yyyymmddhh24miss') from dual;
时区查询:
SELECT SESSIONTIMEZONE FROM DUAL;
select TZ_OFFSET(sessiontimezone) from dual;
带时区的转换:
select TO_TIMESTAMP('19700101','yyyymmdd') + 1235728935/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24 from dual;
select (to_date('2009-2-27 18:02:15','yyyy-mm-dd hh24:mi:ss') - to_date('1970-1-1','yyyy-mm-dd'))*86400- TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 from dual
转换过程中,遇到时区问题,检查数据库,
select TZ_OFFSET(sessiontimezone) from dual;
所以直接在计算公式中加了8小时:
select to_date(19700101,'yyyymmdd') + (1106796974.717+8*60*60)/86400 from dual
--转自