In Oracle Database, the TZ_OFFSET() function returns the time zone offset corresponding to the argument based on the date the statement is executed.

Syntax

The syntax goes like this:

TZ_OFFSET({ 'time_zone_name'
          | '{ + | - } hh : mi'
          | SESSIONTIMEZONE
          | DBTIMEZONE
          }
         )

Examples

Here are some examples to demonstrate.

Time Zone Region Name

You can pass a valid time zone region name:

SELECT TZ_OFFSET('Australia/Darwin')
FROM DUAL;

Result:

+09:30

Here’s how to get a list of supported time zones in your system.

Time Zone Offset

You can pass a time zone offset, in which case it simply returns itself:

SELECT TZ_OFFSET('-10:00')
FROM DUAL;

Result:

-10:00

The SESSIONTIMEZONE Function

The SESSIONTIMEZONE function returns the time zone of the current session. You can use this to get the time zone offset of your current session.

Example:

SELECT 
    SESSIONTIMEZONE,
    TZ_OFFSET(SESSIONTIMEZONE)
FROM DUAL;

Result in my session:

      SESSIONTIMEZONE    TZ_OFFSET(SESSIONTIMEZONE) 
_____________________ _____________________________ 
Australia/Brisbane    +10:00                       

The DBTIMEZONE Function

The DBTIMEZONE function returns the time zone of the database. This may or may not be the same as the SESSIONTIMEZONE on your system.

Example:

SELECT 
    DBTIMEZONE,
    TZ_OFFSET(DBTIMEZONE)
FROM DUAL;

Result:

   DBTIMEZONE    TZ_OFFSET(DBTIMEZONE) 
_____________ ________________________ 
+00:00        +00:00                  

Null Arguments

Passing null results in null:

SET NULL 'null';
SELECT TZ_OFFSET(null)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Argument

Calling the function with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT TZ_OFFSET()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT TZ_OFFSET()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: