Oracle Timezone: Clarification of 2 Timezone Setting

Product: Oracle Database
Version: All
OS: All

History

Many years ago, Oracle database relies on OS clock, as well as no date column type that can store timezone setting (similar to MS SQL Server today).

Timezone aware column type, TIMESTAMP WITH TIME ZONE, introduced much later, but many DBA and developers don't have a good knowledge about OS timezone.

Date DB Function

In Oracle 12c to 21c, DB has following 4 date functions, which has different behavior on timezone

• sysdate - Uses OS timezone
• E.g. select sysdate from dual;
• E.g. insert into [user table] values (sysdate);
• systimestamp - Uses OS timezone
• E.g. select systimestamp from dual;
• E.g. insert into [user table] values (systimestamp);
• current_date - uses DB session timezone, similar to sysdate
• E.g. select sessiontimezone from dual;
• E.g. insert into [user table] values (sessiontimezone);
• LOCALTIMESTAMP
•  - uses DB session timezone, similar to systimestamp
• E.g. select sessiontimezone from dual;
• E.g. insert into [user table] values (sessiontimezone);

Timezone Configuration At DB Level

I won't discuss OS timezone configuration, as this post is mainly clarifying DB level timezone setting that confused many DBA or developers.

In above DB functions, the DB session's timezone depends on sessiontimezone. Default is DB timezone, which set by following:

• ALTER DATABASE SET TIME_ZONE = 'US/New_York';
• CREATE DATABASE ..... TIME_ZONE = 'US/New_York';

At user level, you can configure it using

• ALTER SESSION SET TIME_ZONE='US/Eastern';

You can check its current value by using:

• sessiontimezone - uses DB session timezone, similar to sysdate
• E.g. select sessiontimezone from dual;

Troubleshooting Highlight

When verifying it, always display the sessiontimezone together with 1 systimestamp, and 1 current_date, e.g.

select sessiontimezone , sysdate, current_date from dual;

This will ensure that you cover both OS and DB timezone, and displaying DB timezone value

Note: systimestamp will display OS timezone value

Ref: Oracle 19c DB Time Functions: https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html