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;