Timezone mismatch between DBMS_SCHEDULER and Session

[zachy]

Hey,
We are facing an issue:
Suppose table PRODUCT with column VERSION TIMESTAMP(6) WITH LOCAL TIME ZONE
when I call SELECT * FROM PRODUCT as user with SESSIONTIMEZONE=Europe/Prague it returns 24.09.24 15:13

We have a periodic DBMS_SCHEDULER JOB where we rely on same timezone=Europe/Prague, which can be simulated as

BEGIN
  DBMS_SCHEDULER.RUN_JOB('JEF_Prosperita_Calc', FALSE); -- means USE_CURRENT_SESSION = false, resulting taking from system settings
END; 
/

The query inside scheduler returns 24.09.24 13:13, meaning it does not respect the value of DBTIMEZONE

I tried to set it up using docker environment variables

  database:
    image: gvenzl/oracle-free:latest
    ...
    environment:
      ...
      TZ: Europe/Prague
      ORA_SDTZ: Europe/Prague
    volumes:
      - ./oracle/data:/opt/oracle/oradata
      - ../build/oracle/init_scripts:/container-entrypoint-initdb.d
    ...

Also i tried to use init sh script

sqlplus -s / as sysdba << EOF
-- Root container setup

SET SERVEROUTPUT ON;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS current_container FROM DUAL;

ALTER DATABASE SET TIME_ZONE = 'Europe/Prague';

BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
    attribute => 'default_timezone',
    value     => 'Europe/Prague'); -- Replace with your preferred timezone
END;
/

SELECT DBTIMEZONE FROM DUAL;
DECLARE
  val VARCHAR2(100);
BEGIN
  DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('default_timezone', val);
  DBMS_OUTPUT.PUT_LINE('Root Default Timezone: ' || val);
END;
/

   -- Commit the change
   SHUTDOWN IMMEDIATE;
   STARTUP;

SET SERVEROUTPUT ON;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS current_container FROM DUAL;

SELECT DBTIMEZONE FROM DUAL;
DECLARE
  val VARCHAR2(100);
BEGIN
  DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('default_timezone', val);
  DBMS_OUTPUT.PUT_LINE('Root Default Timezone: ' || val);
END;
/
   exit;
EOF

Is there another way to set it up?

Thanks for any advice.

[gvenzl]

Hi @zachy,

Thanks for using these images!

I have not yet had time to look into this in detail, but reading through the documentation, it appears what's important is the start_date with which the DBMS_SCHEDULER.CREATE_JOB() was invoked:

The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York ensures that daylight saving adjustments are automatically applied. If instead, the time zone of the start_date is set to an absolute offset, such as '-5:00', then daylight savings adjustments are not followed and your job execution is off by an hour for half the year.

When start_date is NULL, the Scheduler determines the time zone for the repeat interval as follows:

It checks whether or not the session time zone is a region name. The session time zone can be set by either:

Issuing an ALTER SESSION statement, for example:

SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
Setting the ORA_SDTZ environment variable.

If the session time zone is an absolute offset instead of a region name, the Scheduler uses the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler uses the time zone of systimestamp when the job or window is enabled.

Perhaps this helps in the meantime.