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.
Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE value. DBTIMEZONE. Returns the value of the database time zone. The value is a time zone offset or a time zone region name.
Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing.
You can check the session time zone by issuing the SQL in Listing 7. SQL> alter session set TIME_ZONE='-03:00'; Session altered.
You can replace the UTC offset with the TZR (time zone region) format element. The following expression specifies US/Pacific for the time zone region: TIMESTAMP '1999-01-15 8:00:00 US/Pacific'
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With