CODEWITHSUNDEEP
oracle11gtimezone
Vivek
Vivek

Reputation: 13238

What is the use of SESSIONTIMEZONE and DBTIMEZONE in oracle

Other than getting the session timezone offset and database timezone offset, is there any other use/role of SESSIONTIMEZONE and DBTIMEZONE in oracle database.

What I want to know is what are the implications of changing SESSIONTIMEZONE and DBTIMEZONE values in terms of inserting/retrieving dates to/from the database.

Upvotes: 6

Views: 3406

Answers (2)

Vadzim
Vadzim

Reputation: 26180

sysdate and dbtimezone different in Oracle Database explains difference between dbtimezone, system timezone and sessiontimezone.

And here is how to sync sessiontimezone to system timezone if needed:

begin execute immediate 'alter session set time_zone = ''' || to_char(systimestamp, 'TZR') || ''''; end;

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Session and db time zones are used in these function.
- systimestamp timestamp in dbtimezone.
- current_timestamp timestamp in sessiontimezone.

And probably in many other places. I'm sure that change will affect dbms_scheduler.
Oracle also is using session timezone during implicit conversion from datetime without timezone to timestamp with time zone

declare
 with_dbtimezone    TIMESTAMP WITH TIME ZONE := systimestamp; --dbtimezone 
 with_sesione_timezone  TIMESTAMP WITH TIME ZONE := current_timestamp; --sesione_timezone
 no_time_zone  TIMESTAMP := with_dbtimezone; -- remmove timezone from ;
 implicitit_converiosn  TIMESTAMP WITH TIME ZONE := no_time_zone;
begin 
 dbms_output.put_line(to_char(with_dbtimezone,'YYYY-MM-DD hh24:mi:ss TZR')); 
 dbms_output.put_line(to_char(with_sesione_timezone,'YYYY-MM-DD hh24:mi:ss TZR'));
 dbms_output.put_line(to_char(no_time_zone,'YYYY-MM-DD hh24:mi:ss TZR'));
 dbms_output.put_line(to_char(implicitit_converiosn,'YYYY-MM-DD hh24:mi:ss TZR'));
end;

Upvotes: 2

Related Questions