Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Date Functions / Oracle DBTIMEZONE

Oracle DBTIMEZONE

The Oracle DBTIMEZONE function returns the database time zone value.

Syntax

The DBTIMEZONE syntax is straightforward that 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?
  • YesNo
Previous Oracle FROM_TZ
Next Oracle ROUND

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Date Functions

  • ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEXT_DAY
  • NEW_TIME
  • ROUND
  • SESSIONTIMEZONE
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR
  • TO_DATE
  • TRUNC
  • TZ_OFFSET

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.