Below are three ways to return time zone information from a datetime value in Oracle Database.

The EXTRACT() Function

The EXTRACT(datetime) function is used for extracting various datetime parts from a datetime value. This includes the time zone information.

Here’s an example:

SELECT EXTRACT(
TIMEZONE_REGION FROM TIMESTAMP '2035-09-26 10:30:45 Australia/Darwin'
)
FROM DUAL;

Result:

Australia/Darwin

In this case, I used the TIMEZONE_REGION argument to extract the time zone region.

The available arguments for extracting various time zone info are:

• TIMEZONE_HOUR
• TIMEZONE_MINUTE
• TIMEZONE_REGION
• TIMEZONE_ABBR

It’s also possible to use a TIMEZONE_OFFSET argument, although this doesn’t appear in Oracle’s documented syntax for this function.

Here’s an example of getting the time zone abbreviation:

SELECT 
    EXTRACT(TIMEZONE_ABBR FROM TIMESTAMP '2035-09-26 10:30:45 Australia/Darwin')
FROM DUAL;

Result:

ACST

Note that, when using the above time zone arguments, the datetime value must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

The EXTRACT() function also accepts arguments for other datetime parts, such as YEAR, MONTH, HOUR, etc.

The TO_CHAR(datetime) Function

We can also use the TO_CHAR(datetime) function as an alternative method to get time zone info from a datetime value.

This function accepts the datetime or interval value as its first argument, and a format model as its second argument. The function then converts the value to a data type of VARCHAR2 in the specified format.

The format model specifies the format for which to return the datetime/interval value. The format model consists of one or more format elements. This enables us to carefully craft the results to reflect our desired format.

If we only want to return time zone info, we can use one of the format elements for that specific purpose.

There are a few different format elements that we can use when returning time zone information from a datetime value.

Let’s start with the TZR format element:

SELECT TO_CHAR(
    TIMESTAMP '2035-09-26 10:30:45 Australia/Darwin', 
    'TZR'
    )
FROM DUAL;

Result:

AUSTRALIA/DARWIN

This format element returns the time zone region. If the datetime value only includes the time zone offset, then that is returned instead.

The TZD format element can be used to return daylight savings information. It returns the time zone information in an abbreviated form.

Example:

SELECT TO_CHAR(
    TIMESTAMP '2035-09-26 10:30:45 Australia/Darwin', 
    'TZD'
    )
FROM DUAL;

Result:

ACST

Alternatively, we can use the TZH and TZM format elements to return the time zone hours and minutes, based on the time zone offset.

SELECT TO_CHAR(
    TIMESTAMP '2035-09-26 10:30:45 Australia/Darwin', 
    'TZH:TZM'
    )
FROM DUAL;

Result:

+09:30

The TZ_OFFSET() Function

The TZ_OFFSET() function returns the time zone offset corresponding to the argument based on the date the statement is executed.

Here’s an example of using TZ_OFFSET() to return the time zone offset based on the time zone region returned by the EXTRACT() function:

SELECT TZ_OFFSET(
    EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '2035-09-26 10:30:45 Australia/Darwin')
)
FROM DUAL;

Result:

+09:30

We can also pass SESSIONTIMEZONE or DBTIMEZONE to TZ_OFFSET() to return the time zone of our current session and database respectively:

SELECT 
    TZ_OFFSET(SESSIONTIMEZONE) AS "Session",
    TZ_OFFSET(DBTIMEZONE) AS "DB"
FROM DUAL;

Result:

   Session        DB 
__________ _________ 
+10:00     +00:00