The Oracle DBTIMEZONE function returns the database time zone value.
Syntax
The DBTIMEZONE syntax is straightforward that requires no argument:
1 | DBTIMEZONE |
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:
1 2 3 4 | SELECT DBTIMEZONE FROM dual; |
If you want to change the database time zone, you use the ALTER DATABASE statement as follows:
1 | ALTER DATABASE SET TIME_ZONE = 'Europe/London'; |
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:
1 2 3 4 | SELECT DBTIMEZONE FROM dual; |
The output is what we expected:
1 | Europe/London |
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:
1 2 3 4 5 6 7 8 9 10 11 12 | 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'; |
In this tutorial, you have learned how to use the Oracle DBTIMEZONE function to get the database time zone.