In this Oracle tutorial, we will check different ways to check the timezone in an Oracle database. Also, I will show you how to set the database timezone and session timezone in Oracle.
How to Check Oracle Database Timezone
Time zones are used because it is easy for the clients in frequent communication to keep the same time. There are two ways of checking the database Timezone in Oracle. Namely,
- Through Command (CMD) prompt
- Through the Oracle SQL Developer tool
Check Oracle Database Timezone through CMD
Before checking Database Timezone through CMD, connecting to the Oracle Database in CMD is necessary. In the first two steps, we are connecting to the Oracle database. And follow the other steps to check the database timezone.
Step-1:
Open the Command prompt by typing CMD in the search bar and hit enter.
Step-2:
Then type the below syntax and hit enter to connect to the Oracle database. Here the
- sqlplus is the name of the database.
- The system is our username, and after the slash is our password for the database.
sqlplus system/oraclepwd as SYSDBA.
Step-3:
Now write the below command to check Oracle Database time in the command prompt.
SELECT DBTIMEZONE FROM DUAL;Below is the output for the Database Timezone in Oracle.
This shows the default timezone while we install the database. Dual means it’s like a table that is automatically created by Oracle Database with the data dictionary.
DBTIMEZONE gives the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name (America/Newyork), depending upon the user specified the database time zone value in the most created or altered database.
How to Check Oracle Database and Session Timezone
By using the SELECT command, we can check Database Timezone and Session Timezone in Oracle Database. The difference between DBTIMEZONE and session timezone is DBtimezone reflects the DBSERVER timezone whereas the session timezone reflects the session timing.
SESSIONTIMEZONE returns the time zone value of the current session. The return type is a time zone offset value or a time zone region name, depending on how the user declared the session time zone value in Oracle Database.
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;Below is the output for the above syntax.
How to Set Oracle Database Timezone
To specify an alternate time zone (the Select another time zone option), we have to select a first value in the Region or country field to narrow the list of time zones to select from in the Time zone field. We will see in the below syntax how to set Database Timezone in Oracle.
ALTER DATABASE SET TIME_ZONE='timezone';Since this is general syntax, we will see the same with examples of different timezone.
Example 1- Set Database Timezone in Values
Let’s see an example of the Database Timezone with the values. The value range will be plus or minus. If we want to change the timezone then go for the below syntax ALTER with the timezone you want to change.
ALTER DATABASE SET TIME_ZONE='-06:00';
Example 2- Set Database Timezone in String
Let’s see an example of a database time zone using the string value. Here string value is a country name that we took here in America/ Newyork. This command takes either a named region such as America/Newyork or an absolute offset from UTC.
ALTER DATABASE SET TIME_ZONE='America/New_york';
How to Set the Session Timezone in Oracle Database
The session time zone can be set by using the values or the string value, let’s look into examples for better understanding. This is also what we can do by offset value or string value.
The general syntax to set the session Timezone is
ALTER SESSION SET TIME_ZONE='timezone';Example-1 Set the Session Time Zone Using the Offset Value
The session time zone can be set by denoting the offset value of the time zone. Below is an example. By default, Oracle chooses the time zone as the operating system during the installation of Oracle. If we want to set the database time zone at create time then use SET TIME_ZONE syntax.
ALTER SESSION SET TIME_ZONE='+06:30';
Example-2 Set the Session Time Zone Using the String Value
The session time zone can be set by declaring the string value of the time zone. Here the string value is the country name with Canada/Mountain. Here if we want to change the session timezone use the below syntax.
ALTER SESSION SET TIME_ZONE='Canada/Mountain';
This is how we can check Timezone in the command prompt (CMD).
Check Oracle Database Timezone using Oracle SQL Developer Tool
We will see database time and session time in the Oracle database from the Oracle SQL developer tool.
- To check the database timezone in the Oracle database. Type the below query in the query builder page and click the Run icon (Green color) at the top.
Select DBTIMEZONE FROM DUAL;
- Here we are giving the database timezone and session timezone together. Below is the syntax and output.
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;
- Now Type all the below command which has been given for the command prompt, and click the Run icon for all the command one by one and the output is below,
SELECT DBTIMEZONE FROM DUAL;
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;
ALTER DATABASE SET TIME_ZONE ='-06:00';
ALTER DATABASE SET TIME_ZONE ='America/New_york';
ALTER SESSION SET TIME_ZONE ='+06:30';
ALTER SESSION SET TIME_ZONE='Canada/Mountain';
How to Check Offset of Region Name in Oracle
If the user wants to see the region name with timings then he has to use the tz_offset keyword. Below is the syntax with the output. TZ_OFFSET returns the time zone offset value related to the argument based on the date statement is executed.
select tzn.tzabbrev, tzname, tz_offset(tzname)
from V$TIMEZONE_NAMES tzn;
This is the method, to check the values with the region in the Oracle database.
How to Check Current Timezone in Oracle Database
Let’s see how to check the current Timezone in the Oracle database. Follow the below syntax to do so. Here sysdate represents the system date whereas dd-mm-yy denotes date, month, and year while hh24mi indicates per day 24 hours.
Select to_char(sysdate, 'dd-mm-yy hh24:mi') from dual;
In this way, we can see the current time in Oracle Database.
Conclusion
In this Oracle tutorial, I have explained how to check the timezone of an Oracle database. Also, I explained how to set the Oracle database timezone using the command prompt as well as by using the Oracle SQL developer tool.
You may like to read the following articles:
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.