Setting database and session time zone in Oracle

Oracle selects the operating system’s time zone by default during installation. To set the database time zone when creating the database, use SET TIME_ZONE in the CREATE DATABASE statement.

Following are the steps to set the time zone at database level

-- Check the time zone
SELECT dbtimezone FROM DUAL;
DBTIME
------
+00:00

--Change the time zone of database
ALTER DATABASE SET TIME_ZONE='Europe/London';
OR
ALTER DATABASE SET TIME_ZONE='-05:00';

--restart the database
Shutdown immediate;

startup

-- Check the time zone
SELECT dbtimezone FROM DUAL;
DBTIME
------
-05:00

Establish the Time Zone at the Session Level

-- use alter session commands
ALTER SESSION SET TIME_ZONE=local;
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
ALTER SESSION SET TIME_ZONE='+10:00';
SQL> SELECT sessiontimezone FROM DUAL;
SESSIONTIMEZONE
---------------
+05:30
SQL> alter session set time_zone='+10:00';
Session altered.

SQL> SELECT sessiontimezone FROM DUAL;
SESSIONTIMEZONE
---------------
+10:00

Converting Timezones Using the TZ Function in Oracle

SELECT FROM_TZ(CAST(TO_DATE('2018-11-02 03:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), '+5:30') AT TIME ZONE '-6:00' "West Coast Time" FROM DUAL;