0

Based on this question and the respective answers I made the next table:

enter image description here

Then I have the next questions:

  1. What is the role of the Database time in this?
  2. There is a way of obtain the time zone of the Database OS?
Community
  • 1
  • 1
rlartiga
  • 429
  • 5
  • 21

1 Answers1

1

There is a way of obtain the time zone of the Database OS?

Yes, you can do

SELECT TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual;
or
SELECT EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) FROM dual;

What is the role of the Database time in this?

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

Take following analogy to understand DBTIMEZONE and TIMESTAMP WITH LOCAL TIME ZONE: Whenever you have to work with time zones a common approach is to store all times in UTC and convert the time at application layer according to current user settings.

That is exactly how TIMESTAMP WITH LOCAL TIME ZONE works but on SQL-Level. Only difference: Oracle does not store TIMESTAMP WITH LOCAL TIME ZONE times fixed at UTC time but at DBTIMEZONE time. Hence 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.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thanks a lot! The weird part is that the first query result is "UNKNOWN" and the second one is "-3:00". – rlartiga Aug 19 '16 at 17:46
  • 1
    I assume `TZR` returns only a value if you you a fully qualified region name, e.g. `Europe/Zurich`. Usually DBTIMEZONE is set as UTC offset rather than time zone region, i.e. the offset is constant over the whole year and you don't apply any daylight saving. – Wernfried Domscheit Aug 19 '16 at 18:35
  • The first query will give you exactly the same result as the second, and it does not require a "recognized" time zone, if instead of the format model `'TZR'` you use `'TZH:TZM'`. –  Aug 20 '16 at 00:34