Oracle TZ_OFFSET() Function

Oracle TZ_OFFSET() is a built-in function that returns the time zone offset corresponding to the provided parameter, based on the date of the statement execution.

Oracle TZ_OFFSET() Syntax

Here is the syntax for the Oracle TZ_OFFSET() function:

TZ_OFFSET(tz)

Parameters

tz

Required. It can be a time zone region name, a time zone offset relative to UTC, or the keywords SESSIONTIMEZONE or DBTIMEZONE.

Return Value

The Oracle TZ_OFFSET() function returns the time zone offset corresponding to the provided parameter.

If any of the parameters is NULL, TZ_OFFSET() will return NULL.

Oracle TZ_OFFSET() Examples

Here are some examples that demonstrate the usage of the Oracle TZ_OFFSET() function.

Time Zone Region Name

The Oracle TZ_OFFSET() function allows you to use time zone region names to get their corresponding time zone offsets.

SELECT
    TZ_OFFSET('Europe/Astrakhan') "Europe/Astrakhan",
    TZ_OFFSET('Asia/Shanghai') "Asia/Shanghai",
    TZ_OFFSET('Pacific/Kosrae') "Pacific/Kosrae"
FROM dual;

Output:

Europe/Astrakhan    Asia/Shanghai    Pacific/Kosrae
___________________ ________________ _________________
+04:00              +08:00           +11:00

You can use the following statement to get more time zone region names:

SELECT
    TZNAME
FROM v$timezone_names;

Time Zone Offset

If you provide a time zone offset, the Oracle TZ_OFFSET() function will return this parameter directly:

SELECT
    TZ_OFFSET('+04:00') "+04:00",
    TZ_OFFSET('-04:00') "-04:00"
FROM dual;

Output:

+04:00    -04:00
_________ _________
+04:00    -04:00

SESSIONTIMEZONE

You can use the keyword SESSIONTIMEZONE to make the Oracle TZ_OFFSET() function return the time zone offset of the session time zone:

SELECT
    TZ_OFFSET(SESSIONTIMEZONE) SESSIONTIMEZONE
FROM dual;

Output:

SESSIONTIMEZONE
__________________
+08:00

DBTIMEZONE

You can use the keyword DBTIMEZONE to make the Oracle TZ_OFFSET() function return the time zone offset of the server time zone:

SELECT
    TZ_OFFSET(DBTIMEZONE) DBTIMEZONE
FROM dual;

Output:

DBTIMEZONE
_____________
+00:00

NULL Parameters

If any of the parameters is NULL, TZ_OFFSET() will return NULL.

SET NULL 'NULL';
SELECT
    TZ_OFFSET(NULL)
FROM dual;

Output:

TZ_OFFSET(NULL)
__________________
NULL

In this example, we use the SET NULL 'NULL'; statement to display the NULL value as the string 'NULL'.

Conclusion

Oracle TZ_OFFSET() is a built-in function that returns the time zone offset corresponding to the provided parameter, based on the date of the statement execution.