SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function),该函数用以返回当前的日期与时间，常和DUAL伪表一起合作。SYSDATE实际上指的是SYSDATE()函数，但是因为这个函数没有参量，所以这里可以省略()圆括号。另外SYSDATE函数是用于获取数据库所在的操作系统的当前时间值的，所以它和数据库或会话的时区没有关系。
我们可以使用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). |
具体的用法如：
syntax: select to_char(sysdate,'FORMAT CODES') from dual; select to_char(sysdate,'YEAR-MON-DAY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YEAR-MON-DAY HH24:MI:SS') -------------------------------------------------------------------------------- TWENTY ELEVEN-AUG-WEDNESDAY 21:34:43
与SYSDATE不同，CURRENT_DATE()函数会返回数据库会话所设置的本地时区的当前日期。
我们来具体看一下这2个函数所返回结果的差异:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmac.cn SQL> alter session set nls_date_format='YYYY-MM-DD HH24:mi:ss'; Session altered. SQL> set linesize 200 pagesize 1400; SQL> col sessiontimezone for a20 SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual; CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE ------------------- ------------------- ------ -------------------- 2011-08-24 19:53:09 2011-08-24 19:53:09 +08:00 +08:00 /* 我们调整session的所在时区到+09:00 */ SQL> alter session set time_zone='+09:00'; Session altered. SQL> select current_date, sysdate,dbtimezone,sessiontimezone from dual; CURRENT_DATE SYSDATE DBTIME SESSIONTIMEZONE ------------------- ------------------- ------ -------------------- 2011-08-24 20:56:33 2011-08-24 19:56:33 +08:00 +09:00 /* 可以看到current_date所返回的时间增长了一个小时，而SYSDATE不变 */
另外请注意SYSDATE函数的类型并非是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.
因为sysdate的时钟取自操作系统，所以DBA同样需要关心操作系统本身时区的设置。不当的时区设置可能导致数据库内部的定时作业系统紊乱，主要会影响使用DBMS_JOB调用的一些JOB，DBMS_JOB包不会考虑到时区的因素，而仅仅考虑sydate的值(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则会考虑具体的时区。
此外一些使用夏令时的地区还会面临更复杂的DST问题，详细可以参考ORAganism的文章<Fake lack of TimeZones in DBMS_JOBs>和MOS文档<DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]> &<Troubleshooting DBMS_SCHEDULER and DBMS_JOB [ID 783357.1] >:
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';
Leave a Reply