Oracle DBTIMEZONE

The Oracle DBTIMEZONE function returns the database time zone value.

Syntax

The DBTIMEZONE syntax is straightforward and requires no argument:

DBTIMEZONE  
Code language: SQL (Structured Query Language) (sql)

Return Value

The DBTIMEZONE function returns a character string that represents a time zone offset in the format [+|-]TZH:TZM e.g., -05:00 or a time zone region name e.g., Europe/London.

The value of the database time zone depends on how you specify it in the most recent CREATE DATABASE or ALTER DATABASE statement.

Examples

To get the database time zone, you use the following statement:

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

If you want to change the database time zone, you use the ALTER DATABASE statement as follows:

ALTER DATABASE SET TIME_ZONE = 'Europe/London';
Code language: SQL (Structured Query Language) (sql)

To make the new database time zone take effect, you need to bounce the database.

After bouncing the database, you can issue the following SQL statement to validate if the change has taken place:

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

The output is what we expected:

Europe/London Code language: SQL (Structured Query Language) (sql)

Remarks

If your database has TIMESTAMP WITH LOCAL TIME ZONE columns, you cannot change the database time zone.

Because Oracle normalizes the time data to the current database time zone before storing it in the TIMESTAMP WITH LOCAL TIME ZONE column.

When you query data from the TIMESTAMP WITH LOCAL TIME ZONE column, Oracle converts the time data back to the session time zone.

The following query checks whether the current database has TIMESTAMP WITH LOCAL TIME ZONE columns:

SELECT
  t.owner, 
  t.table_name,
  t.column_name, 
  t.data_type
FROM
  dba_tab_cols t
INNER JOIN dba_objects o ON o.owner = t.owner AND t.table_name = o.object_name
WHERE
  t.data_type LIKE '%WITH LOCAL TIME ZONE' AND 
  o.object_type = 'TABLE' AND
  o.owner = 'OT';
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle DBTIMEZONE function to get the database time zone.

Was this tutorial helpful?