Oracle TZ_OFFSET

The Oracle TZ_OFFSET() function returns the time zone offset from UTC of a valid time zone name or the SESSIONTIMEZONE or DBTIMEZONE function name.

Syntax

The following illustrates the syntax of the Oracle TZ_OFSSET() function:

TZ_OFFSET(value)
Code language: SQL (Structured Query Language) (sql)

Arguments

1) value

The TZ_OFFSET() function accepts one argument which can be a valid time zone name e.g., ‘Europe/London’, a function name of SESSIONTIMEZONE or DBTIMEZONE, or a time zone offset from UTC (which simply returns itself).

Examples

A) Get the time zone offset of a specific time zone

The following statement returns the time zone offset of the Europe/London time zone from UTC:

SELECT
  TZ_OFFSET( 'Europe/London' )
FROM
  DUAL;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

+01:00Code language: SQL (Structured Query Language) (sql)

B) Get the time zone offset of the database time zone

The following example shows the time zone offset of the database time zone from UTC:

SELECT
  TZ_OFFSET( DBTIMEZONE )
FROM
  DUAL;
Code language: SQL (Structured Query Language) (sql)

The output is:

-07:00
Code language: SQL (Structured Query Language) (sql)

C) Get the time zone offset of the session time zone

The following statement returns the time zone offset of the session time zone from UTC:

SELECT
  TZ_OFFSET( SESSIONTIMEZONE )
FROM
  DUAL;
Code language: SQL (Structured Query Language) (sql)

The output is:

-07:00
Code language: SQL (Structured Query Language) (sql)

Remarks

The following table illustrates the valid time zones and their offsets from UTC:

In this tutorial, you have learned how to use the Oracle TZ_OFFSET() function to get the time zone offset from UTC of a time zone name.