了解oracle中的那些时间、时区。

一.oracle日期输出格式定义

我们可以使用NLS_DATE_FORMAT参数或者TO_CHAR函数来获得我们想要的SYSDATE日期格式，具体的格式代码如下：
Format Code     Explanation
YEAR             Year, spelled out
YYYY             4-digit year
MM                 Month (01-12; JAN = 01).
MON             Abbreviated name of month.
MONTH             Name of month, padded with blanks to length of 9 characters.
D                 Day of week (1-7).
DAY             Name of day.
DD                 Day of month (1-31).
DDD             Day of year (1-366).
DY                 Abbreviated name of day.
HH                 Hour of day (1-12).
HH12             Hour of day (1-12).
HH24             Hour of day (0-23).
MI                 Minute (0-59).
SS                 Second (0-59).
SSSSS             Seconds past midnight (0-86399).

二.区分oracle中的几个时间值：

（补充小知识：地球自转是自西向东的,西方较晚被太阳照射，故比东方的时间晚，因此从西向东的时区是以此递增UCT+1-->UTC+8..）

SYSDATE  函数是用于获取数据库所在的操作系统的当前时间值的，所以它和数据库或会话的时区没有关系.该值仅在session建立时被与服务器端同步。
CURRENT_DATE  函数会返回数据库会话所设置的本地时区的当前日期与时间值。该值可在session建立时被与客户端同步。或者通过alter session set time_zone='+08:00'; 修改
SESSIONTIMEZONE  与客户端session所在操作系统时区一致。该值可在session建立时被与客户端同步。或者通过alter session set time_zone='+08:00'; 修改
DBTIMEZONE   为数据库的时区，是DB的一个属性值。影响其保存的timestamp with local timezone类型字段？

在服务器端进入sqlplus：
[oracle@redhat ~]$ export ORACLE_SID=test3
[oracle@redhat ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 10 14:44:37 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size            2230912 bytes
Variable Size          209716608 bytes
Database Buffers      406847488 bytes
Redo Buffers            3354624 bytes
Database mounted.
Database opened.
SQL> show user
USER is "SYS"
SQL> set linesize 200
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select current_date,sysdate,dbtimezone,sessiontimezone from dual;

CURRENT_DATE        SYSDATE        DBTIME SESSIONTIMEZONE
------------------- ------------------- ------ ---------------------------------------------------------------------------
2013-04-10 14:48:28 2013-04-10 14:48:28 +00:00 +08:00

SQL> ! date -R
Wed, 10 Apr 2013 14:48:37 +0800


改变OS时区：
[root@redhat ~]# cp /usr/share/zoneinfo/Europe/Amsterdam /etc/localtime
cp: overwrite `/etc/localtime'? y
[root@redhat ~]# date -R
Wed, 10 Apr 2013 09:06:55 +0200

在服务器端进入sqlplus：
[oracle@redhat ~]$ export ORACLE_SID=test3
[oracle@redhat ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 10 09:07:09 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 200
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select current_date,sysdate,dbtimezone,sessiontimezone from dual;

CURRENT_DATE        SYSDATE                DBTIME SESSIONTIMEZONE
------------------- ------------------- ------ ---------------------------------------------------------------------------
2013-04-10 09:07:49 2013-04-10 09:07:49 +00:00 +02:00


然后从远端windows机器（时区为UTC +08:00）登录上去查看，有何不同？
CURRENT_DATE        SYSDATE                DBTIME SESSIONTIMEZONE
------------------- ------------------- ------ ---------
2013-04-10 15:22:27    2013-04-10 09:22:27    +00:00    Asia/Shanghai

三.SYSDATE、CURRENT_DATE函数的返回类型并非是DATE，而是其特有的类型:

SQL> create table mytime(t1 date);

Table created.

SQL> insert into mytime values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select dump(t1), dump(sysdate), dump(current_date) from mytime;

DUMP(T1)
--------------------------------------------------------------------------------
DUMP(SYSDATE)
--------------------------------------------------------------------------------
DUMP(CURRENT_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,111,8,24,21,31,59
Typ=13 Len=8: 219,7,8,24,20,32,12,0
Typ=13 Len=8: 219,7,8,24,20,32,12,0

以上可以看到 sysdate和current_date都属于TYPE=13，而普通DATE类型为TYPE=12

数据类型type=12代表DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.

四.DBTIMEZONE调整示例

SQL> alter database set time_zone='+10:00';
alter database set time_zone='+10:00'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns


1.select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
  from sys.obj$ o, sys.col$ c, sys.user$ u
  where c.type# = 231
  and o.obj# = c.obj#
  and u.user# = o.owner#;

  查看包含TIMESTAMP WITH LOCAL TIME ZONE列的对象。

2.临时保存TIMESTAMP WITH LOCAL TIME ZONE列的数据

  --创建一个临时表

  create table ttt(order_id number ,oeder_Date date);

  --导入数据

  insert into ttt(order_id,oeder_date) select order_id,order_date from oe.orders

  --修改oe.orders表

  alter table oe.orders drop column order_date
  alter table oe.orders add order_date date

  --将临时表中的数据恢复到oe.orders

  update oe.orders a set order_date = (select oeder_Date from ttt b where a.order_ID = b.order_id);

  --删除临时表

  drop table ttt

3.修改 DBTIMEZONE

  alter database set time_zone='+08:00';

4.查看DBTIMEZONE

  select DBTIMEZONE from dual ;

 

五. Inserting Values into Datetime Data Types

You can insert values into a datetime column in the following ways:

1.Insert a character string whose format is based on the appropriate NLS format value
2.Insert a literal
3.Insert a literal for which implicit conversion is performed
4.Use the TO_TIMESTAMP, TO_TIMESTAMP_TZ, or TO_DATE SQL function

The following examples show how to insert data into datetime data types.

NLSPG751Example 4-1 Inserting Data into a DATE Column

Set the date format.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Create a table table_dt with columns c_id and c_dt. The c_id column is of NUMBER data type and helps to identify the method by which the data is entered. The c_dt column is of DATE data type.

SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);

Insert a date as a character string.

SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');

Insert the same date as a DATE literal.

SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');

Insert the date as a TIMESTAMP literal. Oracle Database drops the time zone information.

SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 America/Los_Angeles');

Insert the date with the TO_DATE function.

SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));

Display the data.

SQL> SELECT * FROM table_dt;

C_ID       C_DT
---------- --------------------
1          01-JAN-2003 00:00:00
2          01-JAN-2003 00:00:00
3          01-JAN-2003 00:00:00
4          01-JAN-2003 00:00:00

NLSPG752Example 4-2 Inserting Data into a TIMESTAMP Column

Set the timestamp format.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

Create a table table_ts with columns c_id and c_ts. The c_id column is of NUMBER data type and helps to identify the method by which the data is entered. The c_ts column is of TIMESTAMP data type.

SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);

Insert a date and time as a character string.

SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');

Insert the same date and time as a TIMESTAMP literal.

SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

Insert the same date and time as a TIMESTAMP WITH TIME ZONE literal. Oracle Database converts it to a TIMESTAMP value, which means that the time zone information is dropped.

SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

Display the data.

SQL> SELECT * FROM table_ts;

C_ID        C_TS
----------  -----------------------------
1           01-JAN-03 02:00:00.000000 AM
2           01-JAN-03 02:00:00.000000 AM
3           01-JAN-03 02:00:00.000000 AM

Note that the three methods result in the same value being stored.

NLSPG753Example 4-3 Inserting Data into the TIMESTAMP WITH TIME ZONE Data Type

Set the timestamp format.

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

Set the time zone to '-07:00'.

SQL> ALTER SESSION SET TIME_ZONE='-7:00';

Create a table table_tstz with columns c_id and c_tstz. The c_id column is of NUMBER data type and helps to identify the method by which the data is entered. The c_tstz column is of TIMESTAMP WITH TIME ZONE data type.

SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);

Insert a date and time as a character string.

SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');

Insert the same date and time as a TIMESTAMP literal. Oracle Database converts it to a TIMESTAMP WITH TIME ZONE literal, which means that the session time zone is appended to the TIMESTAMP value.

SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

Insert the same date and time as a TIMESTAMP WITH TIME ZONE literal.

SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');

Display the data.

SQL> SELECT * FROM table_tstz;

C_ID        C_TSTZ
----------  ------------------------------------
1           01-JAN-03 02:00.00:000000 AM -07:00
2           01-JAN-03 02:00:00.000000 AM -07:00
3           01-JAN-03 02:00:00.000000 AM -08:00

Note that the time zone is different for method 3, because the time zone information was specified as part of the TIMESTAMP WITH TIME ZONE literal.

NLSPG754Example 4-4 Inserting Data into the TIMESTAMP WITH LOCAL TIME ZONE Data Type

Consider data that is being entered in Denver, Colorado, U.S.A., whose time zone is UTC-7.

SQL> ALTER SESSION SET TIME_ZONE='-07:00';

Create a table table_tsltz with columns c_id and c_tsltz. The c_id column is of NUMBER data type and helps to identify the method by which the data is entered. The c_tsltz column is of TIMESTAMP WITH LOCAL TIME ZONE data type.

SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

Insert a date and time as a character string.

SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');

Insert the same data as a TIMESTAMP WITH LOCAL TIME ZONE literal.

SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

Insert the same data as a TIMESTAMP WITH TIME ZONE literal. Oracle Database converts the data to a TIMESTAMP WITH LOCAL TIME ZONE value. This means the time zone that is entered (-08:00) is converted to the session time zone value (-07:00).

SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

Display the data.

SQL> SELECT * FROM table_tsltz;

C_ID        C_TSLTZ
----------  ------------------------------------
1           01-JAN-03 02.00.00.000000 AM
2           01-JAN-03 02.00.00.000000 AM
3           01-JAN-03 03.00.00.000000 AM

Note that the information that was entered as UTC-8 has been changed to the local time zone, changing the hour from 2 to 3.



Choosing a TIMESTAMP Data Type

Use the TIMESTAMP data type when you need a datetime value to record the time of an event. For example, you can store information about the times when workers punch a timecard in and out of their assembly line workstations. The application can be used across time zones. Consider a banking company with offices around the world. It can record a deposit to an account at 11 a.m. in London and a withdrawal of the same amount from the account at 9 a.m. in New York, by normalizing the time zones. The TIMESTAMP data type uses 7 or 11 bytes of storage.

Use the TIMESTAMP WITH TIME ZONE data type when the datetime value represents a future local time or the time zone information needs to be recorded with the value. Consider a scheduled appointment in a local time. The future local time may need to be adjusted if the time zone definition, such as daylight saving rule, changes. Otherwise, the value can become incorrect. This data type is most immune to such impact.

The TIMESTAMP WITH TIME ZONE data type requires 13 bytes of storage, or two more bytes of storage than the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE data types because it stores time zone information. The time zone is stored as a time zone region name or as an offset from UTC. The data is available for display or calculations without additional processing. A TIMESTAMP WITH TIME ZONE column cannot be used as a primary key. If an index is created on a TIMESTAMP WITH TIME ZONE column, it becomes a function-based index.

The TIMESTAMP WITH LOCAL TIME ZONE data type stores the timestamp without time zone information. It normalizes the data to the database time zone every time the data is sent to and from a client. It requires 11 bytes of storage.

The TIMESTAMP WITH LOCAL TIME ZONE data type is appropriate when the original time zone is of no interest, but the relative times of events are important and daylight saving adjustment does not have to be accurate. The time zone conversion that this data type performs to and from the database time zone is asymmetrical, due to the daylight saving adjustment. Because this data type does not preserve the time zone information, it does not distinguish values near the adjustment in fall, whether they are daylight saving time or standard time. This confusion between distinct instants can cause an application to behave unexpectedly, especially if the adjustment takes place during the normal working hours of a user.

Note that some regions, such as Brazil and Israel, that update their Daylight Saving Transition dates frequently and at irregular periods, are particularly susceptible to time zone adjustment issues. If time information from these regions is key to your application, you may want to consider using one of the other datetime types.

六. 因为sysdate的时钟取自操作系统，所以DBA同样需要关心操作系统本身时区的设置。

不当的时区设置可能导致数据库内部的定时作业系统紊乱，主要会影响使用DBMS_JOB调用的一些JOB，DBMS_JOB包不会考虑到时区的因素，
而仅仅考虑sysdate的值
(DBMS_JOB uses the “date” datatype to store the start date/time and does not store/use timezone related information.
JOBS who are scheduled are always executed when sysdate => next execution time. )。
如果使用DBMS_SCHEDULER则会考虑具体时区。


DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.2 - Release: 9.0.1 to 11.2
Information in this document applies to any platform.
Purpose
This note gives a overview of how DBMS_SCHEDULER / DBMS_JOB react on DST transitions and what
to know when using timezone information with jobs.
Scope and Application
To be used when DBMS_SCHEDULER jobs run at a wrong time after a DST change or you have output
from jobs selecting from TIMESTAMP WITH LOCAL TIMEZONE columns that is not correct.
If things about timezones are not clear please have first a look at Note 340512.1 Timestamps
& time zones - Frequently Asked Questions.
DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained.
When running trough DBMS_SCHEDULER or DBMS_JOB a job output using TIMESTAMP WITH LOCAL
TIMEZONE gives the wrong time.This happens when you use DBMS_SCHEDULER or DBMS_JOB to run jobs
who select information from TIMESTAMP WITH LOCAL TIMEZONE fields.
The time in a TIMESTAMP WITH LOCAL TIMEZONE that will be returned depends on the SESSIONTIMEZONE.

select sessiontimezone from dual;

If a TIMESTAMP WITH LOCAL TIMEZONE field contains for example "20-DEC-2007 15:15 -08:00" and
your session timezone is also -08:00 you will see 15:15.
If you change the session timezone to (for example) -07:00 then you will see 16:15,
witch is normal and intended.

Jobs using DBMS_SCHEDULER and DBMS_JOB however use a SESSIONTIMEZONE set to UTC (= +00:00 ).

Hence, above example will give 15 + 8 = 23:15 when selected trough a job.

If you use jobs that provide TIMESTAMP WITH LOCAL TIMEZONE then you may need to
included in the job an alter session to change the SESSIONTIMEZONE.

execute immediate 'alter session set time_zone = ''-08:00''';

If you want the job to follow DST rules then you need to use a named timezone that
follows the DST rules you want to use:
execute immediate 'alter session set time_zone = ''US/Pacific''';

There is Enhancement Request 5479680 - INCORPORATE SESSIONTIMEZONE INFORMATION INTO
JOB INFORMATION to a) use the session timezone of the submitting session as timezone and b)
have a way to see with what timezone a job is submitted.
Job runtime time after DST has changed.
When using DBMS_JOB:
DBMS_JOB uses the "date" datatype to store the start date/time and does not store/use timezone
 related information. JOBS who are scheduled are always executed when sysdate => next execution time.
This means for DST transitions that:

* Assuming here that during DST the time is going FORWARD from 1:59 am directly to 3:00 am (skip on hour)
- any job scheduled between 2:00 am and 2:59 am will only be started at 3:00 am seen the period between
2:00 am and 2:59 am does not exist. This means ALL jobs scheduled between 2:00 am and 2:59 am are started
at 3:00 am at once.

* Assuming here that during DST the time is going BACK from 2:59 am to 2:00 am (= add one hour) the
jobs will be executed in the "first" 2:00 am - 2:59 am timeslot, if the interval is more then
one hour then the next execution will simply the correct time - aldo the actual "absolute"
interval will be one hour more then normal. If the "interval" is less then one hour then they will
NOT be executed the "second time" time the 2:00 am- 2:59 am timeslot is there because the "next execution time"
will be > 2:59 am. So if you have for example a job that runs every 5 minutes this will NOT be executed
during the "second time" the 2:00 am - 2:59 am timeslot is there until "locale time" becomes 3:00 am (or later).

Please also note that the next execution time is calculated AFTER the job has finished.
A known issue: Note 1328496.1 Ora-01878: Specified Field Not Found In Datetime Or Interval in
Alert.log after DST change When using DBMS_SCHEDULER.
DBMS_SCHEDULER uses a TIMESTAMP WITH TIMEZONE field to store the starting time (and other time related
information) of a job, unlike DBMS_JOB who uses a "date" column who has no concept of timezones.

When checking the various DBMS_SCHEDULER time fields make sure you are using a NLS_TIMESTAMP_TZ_FORMAT
that display's the timezone information

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

or for US customers:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY/MM/DD HH24:MI:SS TZR TZD';

By using the "TZR TZD" mask you can quickly see in what format the data is stored/defined:

select start_date, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='';

* If it returns a offset (ex: -08:00) then the job time is defined with a offset.
Timezone offsets are by nature NOT "DST aware" seen they mean a fixed offset from UTC.
So any job using a offset as timezone will run after a DST change at a wrong time.

* If it returns a named timezone (ex: Japan ) then the time is defined with
that timezone name but that timezone has no DST rules.
Some named timezones are also not "DST aware" ( Japan for example) because that region simply does not use DST.

* If it returns a named timezone and a DST prefix (ex: US/Pacific PST )
then the time is defined with that timezone name and that timezone is DST aware.

To have a job that automatically adjusts for DST you need to make sure it's defined
with the proper named timezone name that actually uses DST rules.

The DST information in Oracle is sometimes updated, when using a named timezone please check
Note 412160.1 Updated Time Zones in Oracle Time Zone File patches.
that note has a list of named timezones that changed and so you can see if you need to
update the Oracle DST information.
Please note that it's a good idea to update the Oracle DST definitions when using named
timezones but that applying "a DST patch" will not always solve all scheduler problems.
Most "scheduler dst" problems are due the usage of a offset, not a named timezone, when creating the jobs.

Good to know:
* In oracle the session timezone (select sessiontimezone from dual;) defaults to a offset ( like +05:00),
even if the Operating system "TZ" variable is set to a named TZ,
unless the ORA_SDTZ is set in the client (!) environment (or registry) with a *oracle* TZ name.

* DBMS_SCHEDULER has no relation to or does not use the database timezone.

When you submit a job using DBMS_SCHEDULER Oracle will use a different source to populate the timezone depending on:

a) When start_date is NULL, DBMS_SCHEDULER will determine the time zone for the repeat interval as follows:

1. It will check whether the session time zone is a region name. The session time
zone can be set to a named timezone by either:
* Issuing an ALTER SESSION statement, for example:
SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
* Setting the ORA_SDTZ environment variable on the client (!) side.

2. If the session time zone is an absolute offset instead of a region name, the Scheduler
will use the value of the DEFAULT_TIMEZONE Scheduler attribute.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');

PL/SQL procedure successfully completed.

To see the current defined DEFAULT_TIMEZONE Scheduler attribute you can issue:

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

Session altered.

SQL> select dbms_scheduler.stime from dual;

STIME
---------------------------------------------------------------------------
06/03/2008 11:58:06 US/EASTERN EST

3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of
systimestamp when the job or window is enabled .The timezone returned by systimestamp is always a OFFSET.

Conclusion: make sure your DEFAULT_TIMEZONE Scheduler attribute is set, and check the
sessiontimezone if needed seen this may take precedence.

Note: SELECT DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME FROM DUAL;
is not related to the DEFAULT_TIMEZONE.
DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME provides the TimeZone set on the OS level trough the
TZ variable, it will return the named timezone only if the OS TZ setting is also known in Oracle.
Only when no OS TZ variable is set it will report the DEFAULT_TIMEZONE.

b) When start_date is not NULL then DBMS_SCHEDULER will use:

1. the timezone specified in the start time
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
begin
DBMS_SCHEDULER.DROP_JOB ('GIVETZ_TEST');
end;
/
begin
dbms_scheduler.create_job('GIVETZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00 CET', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='GIVETZ_TEST';

begin
DBMS_SCHEDULER.DROP_JOB ('GIVETZ_TEST');
end;
/
begin
dbms_scheduler.create_job('GIVETZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00 +01:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='GIVETZ_TEST';

2. The SESSION timezone when not specified in the start time.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = '+01:00';
begin
DBMS_SCHEDULER.DROP_JOB ('NODTZ_TEST');
end;
/
begin
dbms_scheduler.create_job('NODTZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date,next_run_date from
dba_scheduler_jobs where job_name='NODTZ_TEST';

ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('NODTZ_TEST');
end;
/
begin
dbms_scheduler.create_job('NODTZ_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>'19/11/2007 12:00:00', enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date,next_run_date from
dba_scheduler_jobs where job_name='NODTZ_TEST';

3. The SESSION timezone when using CURRENT_TIMESTAMP

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = '+01:00';
begin
DBMS_SCHEDULER.DROP_JOB ('CURRTS_TEST');
end;
/
begin
dbms_scheduler.create_job('CURRTS_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>current_timestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='CURRTS_TEST';

ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('CURRTS_TEST');
end;
/
begin
dbms_scheduler.create_job('CURRTS_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>current_timestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=01');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='CURRTS_TEST';

4. The SYSTIMESTAMP offset when using SYSTIMESTAMP

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = 'CET';

begin
DBMS_SCHEDULER.DROP_JOB ('SYSTIME_TEST');
end;
/
begin
dbms_scheduler.create_job('SYSTIME_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>systimestamp, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='SYSTIME_TEST';
The timezone returned by systimestamp is always a OFFSET.

5. the SESSION timezone when using SYSDATE.

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET TIME_ZONE = '+01:00';

begin
DBMS_SCHEDULER.DROP_JOB ('SYSDATE_TEST');
end;
/
begin
dbms_scheduler.create_job('SYSDATE_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>sysdate, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='SYSDATE_TEST';

ALTER SESSION SET TIME_ZONE = 'CET';
begin
DBMS_SCHEDULER.DROP_JOB ('SYSDATE_TEST');
end;
/
begin
dbms_scheduler.create_job('SYSDATE_TEST','PLSQL_BLOCK','begin null; end;',
start_date=>sysdate, enabled=>true,repeat_interval => 'freq=daily;byhour=12;byminute=00');
end;
/

select start_date, repeat_interval, last_start_date, next_run_date from
dba_scheduler_jobs where job_name='SYSDATE_TEST';

So the best option if to always specify a full start date with a timezone name that uses
the DST rules you expect or to use for example CURRENT_TIMESTAMP and set the session timezone
first to the timezone name that uses the DST rules you expect.

You can also redefine existing jobs if you notice they are defined with a offset.
A example using a explicit named timezone:
begin
dbms_scheduler.set_attribute('SYSDATE_TEST','start_date',to_timestamp_tz
('Europe/London 12:00 20-NOV-2007','TZR HH24:MI DD-MON-RRRR'));
end;
/
select start_date, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs
where job_name='SYSDATE_TEST';