--Example 1
SELECT TZ_OFFSET('US/Eastern') AS tz_test
FROM dual;

--Example 2
SELECT TZ_OFFSET('-02:00') AS tz_test
FROM dual;

--Example 3
SELECT TZ_OFFSET(SESSIONTIMEZONE) AS tz_test
FROM dual;

--Example 4
SELECT TZ_OFFSET(DBTIMEZONE) AS tz_test
FROM dual;

--Example 5
SELECT
TZ_OFFSET(SESSIONTIMEZONE) AS tz_test,
SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3))) * (
  TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 2, 2)) +
  TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 5, 2)) / 60
) AS tz_offset_num
FROM dual;



SELECT * FROM V$TIMEZONE_NAMES;

http://www.databasestar.com/oracle-tz_offset/

A few examples of the TZ_OFFSET function.

Oracle TZ_OFFSET Function

sql, Oracle

SQL