How to Check Timezone in Oracle Database 19c (Simple Methods)

Understanding time zones can be helpful for maintaining data integrity and synchronization when data remains scattered across different geographic locations. Oracle Database 19c offers a simple query-based process to manage the time zone information more effectively.

It allows database administrators and developers to synchronize their applications. In the sections that follow, we will explore the simple steps to check the time zone in Oracle Database 19c.

How to Find Timezone in Oracle Database 19c?

In Oracle Database 19c, you can use the DBTIMEZONE and SESSIONTIMEZONE functions to check the time zones for the database and the current session, respectively.

But, since these functions need to access the DUAL table and retrieve information from it, you must have the appropriate privileges to run them. So, make sure you have the required permission.

1. How to Check the Database Timezone

Run the following query to check the database time zone in Oracle Database 19c. It will return the default time zone of the database.

SELECT DBTIMEZONE FROM DUAL;

Output:

2. How to Check the Session Timezone

The queries used to check the time zone setting for the current session and the time zone of the database are almost the same. Below is the query that returns the session time zone after execution. 

SELECT SESSIONTIMEZONE FROM DUAL;

The returned data represents the time zone setting for the current session, which might differ from the database’s default time zone. 

Frequently Asked Questions and Answers – FAQs

What is the DB time zone in Oracle?

Using the DBTIMEZONE query returns a string of characters that shows a time zone in the [+|-]TZH: TZM format. Where TZH represents the hour, and TZM represents the minute values. These depend on the specification of the most recent CREATE or ALTER DATABASE statement.

How to check the current DB time in Oracle?

You can check the current DB time in Oracle using the CURRENT_TIMESTAMP() function. It provides the date and time of the present session in the corresponding time zone. The output is a value with the TIMESTAMP WITH TIME ZONE data type.

How to check UTC time in Oracle?

To check UTC (Coordinated Universal Time) time in Oracle, use the SYS_EXTRACT_UTC( ) function. You can find the UTC from a date-time value with a time zone offset or region. However, the session time zone can help achieve the same.

Conclusion

You can check the database and session time zone together by running a single-line query. (e.g. SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL). The output will show the database time zone followed by the session time zone in the next line. Doing so removes the hassle of running separate queries.