Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle DBTIMEZONE Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: DBTIMEZONE

Definition:
DBTIMEZONE is a function which returns the current value of Database Time Zone. It can be queried using the example below:

SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00


Please note the return type of function is Time Zone Offset. The format ( [+|-] TZH: TZM) contains the lead (+) and lag (-) with hour and minutes specifications.

Notes

1. Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
2. A Time zone can be converted into Time Zone offset format using TZ_OFFSET function.
Example:

SELECT TZ_OFFSET('America/Menominee') FROM DUAL;

TZ_OFFS
--------
-06:00


3. Time zone is set during database creation or using CREATE DATABASE. It can be altered using ALTER DATABASE command. Database time zone cannot be altered if a column of type TIMESTAMP WITH [LOCAL] TIMEZONE exists in the database. Time zone can be set in Location zone format or [+|-]HH:MM format.
Example,

ALTER DATABASE SET TIME_ZONE='America/Menominee';
Database altered.


ALTER DATABASE SET TIME_ZONE='-06:00';
Database altered.


Once the time zone is set, database must be bounced back to reflect the changes.

4. Difference between SYSDATE and DBTIMEZONE- SYSDATE shows the date-time details provided by the OS on the server. It has nothing to do with TIMEZONE of the database.
5. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.

Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 59 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?