How to Change Session Time Zone

TIME_ZONE

In the previous post, we have checked the time zone in the current session. Now the question is, how to change it? if it doesn't fit our context.

To change the time zone of your session, you may use ALTER SESSION SET to set TIME_ZONE which is a session only parameter.

There're 4 ways to change the session time zone.

UTC Format
LOCAL
Time Zone Region
Database Time Zone

UTC Format

As long as you conform to the format mask of UTC.

[+|-]hh:mi

It ranges from UTC−12:00 in the west to UTC+14:00 in the east. (List of UTC offsets)


SQL> alter session set time_zone = '-08:00';



Session altered.



SQL> select sessiontimezone from dual;



SESSIONTIMEZONE

---------------------------------------------------------------------------

-08:00

LOCAL

LOCAL means the local time zone in your environment.


SQL> alter session set time_zone = local;



Session altered.



SQL> select sessiontimezone from dual;



SESSIONTIMEZONE

---------------------------------------------------------------------------

-04:00

We're back to the original session time zone.

Time Zone Region

You need to use a valid time zone region in Oracle. For example:


SQL> alter session set time_zone = 'Asia/Tokyo';



Session altered.



SESSIONTIMEZONE

---------------------------------------------------------------------------

Asia/Tokyo

Database Time Zone

It's really unusual to set it as database time zone, but it supports.


SQL> alter session set time_zone = dbtimezone;



Session altered.



SESSIONTIMEZONE

---------------------------------------------------------------------------

+00:00

The session time zone has been changed.