Oracle timezone behaves inconsistently

0
votes

I have two DBS - Db1 and Db2. DB1 - "select dbtimezone from dual" returns +00:00. Which means UTC.

DB2- "select dbtime zone from dual" returns -04:00

For both DBs, "SELECT SESSIONTIMEZONE FROM DUAL" returns "Asia/Calcutta".

When I insert a record in a table in DB1 with UTC time, created_time column shows time in UTC - 20-MAY-20 02.47.27.418000000 PM UTC. Note the word UTC here. So, it must be taking from dbtimezone.

When I insert a record in a table in DB2 with UTC time,created_time column shows time zone as Asia/Calcutta. Seems to be taking from session timezone. DB time zone is -04:00. It corresponds to Atlantic time and not Asia time.

The table descriptions of both tables read TIME STAMP WITH TIMEZONE for created_date column. No local time zone mentioned here.

Why does Oracle take dbtimezone in case of DB1 and session time zone in DB2? Why this inconsistency? My aim is to populate the created_time in DB2 in UTC.

1
oracletimezoneutc
Please give 4 digits version for DB1 and DB2. – pifor
what are 4 digits versions ? both are oracle databases. – Praveen Nvs

1 Answers

-1
votes

Maybe this will answer this question: http://www.dba-oracle.com/t_change_timezone.htm

Database time zone is normally set at the database creation but you can manually change it and restart the database like so: 

SQL> alter database set time_zone = '-05:00';
SQL> shutdown immediate
SQL> startup