SELECT DBTIMEZONE FROM DUAL;
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.
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.
SELECT TZ_OFFSET('America/Menominee') FROM DUAL;
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.
ALTER DATABASE SET TIME_ZONE='America/Menominee';
ALTER DATABASE SET TIME_ZONE='-06:00';
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 Code Snippets: