syntax and example of tz_offset function in oracle database query

Oracle TZ_OFFSET Function is used to return the TIMEZONE offset of a value.

Oracle TZ_OFFSET Function Syntax

SELECT TZ_OFFSET(time_zone)
FROM table_name;

‘time_zone’ can be any time zone, a time zone offset from UTC, a sessiontimezone or a dbtimezone.

Using the below SQL SELECT Statement you can view all the Timezones available.

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

Oracle TZ_OFFSET Function is used with the SQL SELECT Statement.

For example, the Oracle TZ_OFFSET query below returns the Time Zone offset of “Asia/Tokyo” zone.

SELECT TZ_OFFSET('Asia/Tokyo')
FROM DUAL;

Will return “+09:00” as the offset for the ASIA/TOKYO time zone.

Oracle TZ_OFFSET can be used with the SQL SESSIONTIMEZONE Function.

For example, the Oracle TZ_OFFSET query below returns the Timezone offset of the current session of the database.

SELECT TZ_OFFSET(SESSIONTIMEZONE)
FROM DUAL;

May return something like “+05:30” depending upon the session’s timezone offset.

Oracle TZ_OFFSET can be used with the SQL DBTIMEZONE to fetch the database time zone offset.

For example, the SQL TZ_OFFSET query below returns the timezone offset for the database.

SELECT TZ_OFFSET(DBTIMEZONE)
FROM DUAL;

May return something like “+00:00” depending upon the database timezone settings.