时区 Oracle deal with different time zone sys_extract_utc

 Method 1:

SELECT TRUNC(SYSTIMESTAMP - NUMTODSINTERVAL( SIGN(EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP))

                         * (ABS(EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)) * 60
                         + ABS(EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP)) )
                         - (偏移量), 'MINUTE' )) 
 

FROM DUAL;

 

 

Method 2: using sys_extract_utc

 

TZ_OFFSET ----需要传入的偏移量 这里单位是分钟，可以根据实际修改单位 in NUMTODSINTERVAL

select sys_extract_utc(systimestamp) UTC,

TRUNC(sys_extract_utc(systimestamp)+NUMTODSINTERVAL(TZ_OFFSET--时间偏移量,'MINUTE')) UTC_PLUS_OFFSET,

systimestamp SYS, current_timestamp LOCAL from dual;

例如： 北京时间 的偏移量是8小时，也就是480分钟

select sys_extract_utc(systimestamp) UTC,
trunc(sys_extract_utc(systimestamp)+NUMTODSINTERVAL(480,'MINUTE')) UTC_PLUS_OFFSET,

systimestamp SYS, current_timestamp LOCAL from dual;

 

select sys_extract_utc(systimestamp) UTC,
to_char(sys_extract_utc(systimestamp)+NUMTODSINTERVAL(480,'MINUTE'),'yyyy-mm-dd hh24:mi:ss') UTC_PLUS_OFFSET,

systimestamp SYS, current_timestamp LOCAL from dual;

---to get timezone name 

select * from V$TIMEZONE_NAMES where tzabbrev ='EST';

--to get tz_offset

SELECT TZ_OFFSET('Europe/London') FROM DUAL;