oracle11g - How to handle Day Light Saving in Oracle database

Question

In one of the Oracle database server it is showing "+01:00" when I fire the "Select dbtimezone from dual" does that mean in summer the clock will shift one hour ahead ?. In another server it is showing "+00:00" does that mean the database server setting is GMT ? but I am using the sysdate in oracle pl/sql. Client is saying the Aix server is on DST so would that mean the DB server will adopt the AIX server setting after clock change ? How to fix this problem.

Answer 1

Answer is: It depends.

In total your database has three time zones

1. Your session time zone: SESSIONTIMEZONE

This you can change by ALTER SESSION SET TIME_ZONE=... at any time. It is relevant for result of

• CURRENT_DATE

• LOCALTIMESTAMP

• CURRENT_TIMESTAMP

  
  

It is also the target time zone when you do CAST({TIMESTAMP/DATE without any timezone} AS TIMESTAMP WITH {LOCAL} TIME ZONE)

Default SESSIONTIMEZONE can be set by environment variable ORA_SDTZ or (on Windows) by registry entry HKLMSOFTWAREWow6432NodeORACLEKEY_%ORACLE_HOME_NAME%ORA_SDTZ (for 32 bit Client), resp. HKLMSOFTWAREORACLEKEY_%ORACLE_HOME_NAME%ORA_SDTZ (for 64 bit Client).

2. The database time zone: DBTIMEZONE

Actually this is not so important in daily use, it is relevant only for TIMESTAMP WITH LOCAL TIME ZONE data type columns and defines the storage format.

This is NOT the timezone of SYSDATE or SYSTIMESTAMP!!!

You cannot change DBTIMEZONE on your database if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data. Otherwise it can be changed with ALTER DATABASE SET TIME_ZONE='...';. The change does not take effect until the database has been shut down and restarted.

DBTIMEZONE is set when database is created. If no time zone is provided while database creation then Oracle defaults to the time zone of the server's operating system.

3. The time zone of database server's operating system:

This time zone is relevant for result of

• SYSDATE

• SYSTIMESTAMP

  
  

Naturally this time zone cannot be changed on database level. In case your home country uses Daylight Saving Times, this time zone may change twice a year. You can interrogate it with SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual;, for instance.

So, if your DB Server OS is setup properly, then you should get summer times from next week on (at least for Europe)