What is the use of SESSIONTIMEZONE and DBTIMEZONE in oracle

Question

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.

Have you already looked at the documentation - e.g. [here](https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#i1006705) and [here](https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#i1006728)? — Alex Poole, Jul 27 '17 at 14:35

score 2 · Answer 1 · answered Jul 27 '17 at 14:34

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;

Nope, `dbtimezone` is not guaranteed to match `systimestamp`'s timezone: https://stackoverflow.com/questions/52531137/sysdate-and-dbtimezone-different-in-oracle-database. — Vadzim, May 12 '20 at 01:03

score 2 · Answer 2 · answered May 12 '20 at 01:01

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;

What is the use of SESSIONTIMEZONE and DBTIMEZONE in oracle

2 Answers2