How to handle Day Light Saving in Oracle database

by

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 HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ (for 32 bit Client), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\ORA_SDTZ (for 64 bit Client).

  1. 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.

  1. 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)

More Related Contents:

  1. Maximum Number of times we can query an oracle database [closed]
  2. How to uninstall / completely remove Oracle 11g (client)?
  3. Oracle trigger after insert or delete
  4. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  5. What is the effect of placing the commit after DML in procedure?
  6. Auto Increment for Oracle
  7. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  8. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  9. Oracle Search all tables all columns for string [duplicate]
  10. NLS_NUMERIC_CHARACTERS setting for decimal
  11. creating parameterized views in oracle11g
  12. Create Pivot view in SQL from a SQL table
  13. How to create a user in Oracle 11g and grant permissions
  14. Table-Valued Functions in ORACLE 11g ? ( parameterized views )
  15. Trigger to enforce M-M relationship
  16. How can I describe a table in Oracle without using the DESCRIBE command?
  17. Functions vs procedures in Oracle
  18. How to insert a column in a specific position in oracle without dropping and recreating the table?
  19. Get the number of days between two dates in Oracle, inclusive of the dates
  20. HikariCP: What database level timeouts should be considered to set maxLifetime for Oracle 11g
  21. passing in table name as plsql parameter
  22. How to use parameters in a ‘where value in…’ clause?
  23. Why cannot I create triggers on objects owned by SYS?
  24. No more data to read from socket
  25. return resultset from function
  26. ORA-12704: character set mismatch
  27. How do I escape a reserved word in Oracle?
  28. SQL Fiddle Output Error
  29. How to list active / open connections in Oracle?
  30. Find out the history of SQL queries

Leave a Comment