oracle 日期与 UTC 转换

日期转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; 
所以直接在计算公式中加了８小时：
select  to_date(19700101,'yyyymmdd') + (1106796974.717+8*60*60)/86400  from dual

转：http://www.cnblogs.com/neru/archive/2011/08/29/2157411.html