Oracle Database: Zoning Conundrum

The Oracle Database has long and complicated relations with time-aware data. It has gotten even more interesting in the internet era and global data processing. As usual, it's a story about how a single line of code at the design time could save many work hours in maintenance.

Traditionally, if you do not set the database instance timezone, it will make the best effort to retrieve this information from an operating system. Naturally, if your operating system has no timezone configured, your database wouldn't have a clue and presume that it is UTC (+0:00), or it could be configured this way - as a database user, you may only guess.

The second source of the date and time details is your workstation. Again, the classic SQL*Net client will pick up everything from the OS and set this information in your session.  

As you may see on my screenshot - SQLcl has the correct session setup, while SQL Worksheet relies on some nameless container. A good example here is scheduled jobs.    

The real fun starts when you have no client - with scheduled jobs running in the background of your database instance and manipulating the data on your behalf. There is a complex and somewhat cumbersome hierarchy that DBMS_SCHEDULER uses to identify the job timezone. Regarding the various sources, starting the documentation order of the source evaluation is

1. When you define the start_date job's attribute as a TIMESTAMP WITH TIMEZONE.
2. If you don't specify the timezone for the start_date, the scheduler will try to use the session timezone information.
3. If there is no start_date defined and the session has no timezone information, the scheduler will use the PDB's default scheduler timezone.  
4. The next one is the CDB's default scheduler timezone if the PDB is not defined.

Even with all that chain of sources, the particular job run may use something you would not expect, especially when Daylight Save Time (aka DST) comes into play. Oracle Support has released detailed explanations and instructions on the matter. Of course, I'm not going to reproduce the copyrighted document, but I want to conclude with two recommendations you should follow:

• If possible, always prefer a region over a specific location and a location over a numeric format. For example, I choose the US/Eastern time zone region over America/New_York and the latest over -05:00.
• Explicitly set the session timezone in your job code.

CREATE OR REPLACE PROCEDURE
             my_tz_avare_job(
                       stop_time in number,
                      job_tz in VARCHAR2:='US/Eastern') AS
BEGIN
     -- Set Job session timezone 
     EXECUTE IMMEDIATE 
        'alter session set timezone='''||job_tz||'''';
     -- Do the usefull job
     null;
END;

The code above will ensure that your job runs in your time and not in London.