Get the Session and Database Timezone Offset in Oracle

If you are scheduling a procedure to query a TIMESTAMP WITH LOCAL TIME ZONE column, you may have noticed that when the database runs the query it returns a different time than when you run that same query yourself. That’s because the database is set to a different timezone than your session.

Returning the Timezone #

To see the database’s timezone:

select dbtimezone from dual;

To see your session’s timezone:

select sessiontimezone from dual;

Altering the Timezone #

You can alter your session’s timezone offset by the name of the timezone or by adding or subtracting the hours yourself.

alter session set time_zone = '-5:0';

alter session set time_zone = 'America/New_York';

If you want to temporarily set the timezone within a procedure, you will need to wrap the alter session statement in an execute immediate statement:

execute immediate 'alter session set time_zone = ''America/New_York''';

View All Available Timezones Names #

Oracle has a list of all the timezone names here:

https://docs.oracle.com/cd/B19306_01/server.102/b14225/applocaledata.htm#i637736

You can also view the list of available timezones by querying your database:

select *
from v$timezone_names;

• Next: Get Bad Login Attempts in Oracle
• Previous: Quickly Taking an ASP.NET App Offline Temporarily