如何确认11.2 RAC Grid Infrastructure的时区

本文档描述如何在11.2 Grid Infrastructure 安装完成后修改其GI的时区time zone 设置。

 

一旦OS默认时区被修改,注意确保以下2点:

1. 对于11.2.0.1 ,确保root、grid、oracle用户的shell环境变量TZ设置正确!
2. 对于11.2.0.2及以上版本,确认 $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt 中的参数设置为正确的时区

 

例如:

 

ech $TZ
TZ=US/Pacific

grep TZ s_crsconfig__env.txt 
TZ=US/Pacific

 

 

若timezone设置时区不正确或存在无关字符可能导致RAC Grid Infrastructure无法正常启动。

确保以上2点保证GI能正常启动,这种因时区不正确导致的启动异常问题常发生在OS、GI已经安装完毕后而时区最后被修改的情景中,若发现OS时间与ohasd.log、ocssd.log等日志中的最新记录的时间不匹配,则往往是由该时区问题引起的。

 

在11.2 CRS之前可以通过init.cssd diag来确认时区设置。

 

 

以下为各OS上Timezone默认配置信息

 

 

Linux

To change: /usr/sbin/timeconfig

To display current setting:

cat /etc/sysconfig/clock
ZONE="America/Los_Angeles"
UTC=true
ARC=false
To find out all valid setting: ls -l /usr/share/zoneinfo

Anything that appears in this directory is valid to use, for example, CST6CDT and America/Chicago.

Note: the "Zone" field in /etc/sysconfig/clock could be different than what's in /usr/share/zoneinfo in OL6.3/RHEL6.3, the one from /usr/share/zoneinfo should be used in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt

hp-ux

To display current setting:

cat /etc/default/tz
PST8PDT
To change: set_parms timezone

To find out all valid setting: ls -l /usr/lib/tztab

Solaris

To display current setting:

grep TZ /etc/TIMEZONE
TZ=US/Pacific
To change, modify TIMEZONE, also run "rtc -z US/pacific; rtc -c"

To find out all valid settings: ls -l /usr/share/lib/zoneinfo

AIX

To display current setting:

grep TZ /etc/environment
TZ=GMT

了解SYSDATE函数

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.askmaclean.com

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';

Script:List NLS Parameters and Timezone

以下脚本用以列出Database-instance-session的NLS参数和所在时区:

REM List NLS Parameters
set linesize 90 pagesize 1400
col Parameter for a40
col Value     for a40
SELECT Parameter, Value FROM NLS_DATABASE_PARAMETERS
/
SELECT Parameter, Value FROM NLS_INSTANCE_PARAMETERS
/
SELECT Parameter, Value FROM NLS_SESSION_PARAMETERS  ORDER BY 1
/
select dbtimezone from dual
/
select sessiontimezone from dual
/

Know Oracle Date And Time Function

Oracle9i provides extended date and time support across different time zones with the help of new datetime data types and functions. To understand the working of these data types and functions, it is necessary to be familiar with the concept of time zones.

This topic group introduces you to the concepts of time such as Coordinated Universal Time, time zones, and daylight saving time.

Objectives

After completing this topic group, you should be able to:

Calculate the date and time for any time zone region using time zone offsets.

Time Zones

The hours of the day are measured by the turning of the earth. The time of day at any particular moment depends on where you are.
100000

The earth is divided into twenty four time zones, one for each hour of the day. The time along the prime meridian in Greenwich, England is known as Coordinated Universal Time, or UTC (formerly known as Greenwich Mean Time, or GMT ). UTC is the time standard against which all other time zones are referenced.

Note: The following topics discuss prime meridian and UTC in more detail.

Coordinated Universal Time

100001

Since time began, the time flow on earth has been ruled by the apparent position of the sun in the sky.

In the past, when methods of transportation made even short travels last for several days, no one, except astronomers, understood that solar time at any given moment is different from place to place.

Around the 1800s with the development of faster modes of transportation and a need for accurate time references for sea navigation, Greenwich mean time (GMT), which later became known as Coordinated Universal Time (UTC), was introduced.

The earth surface is divided into 24 adjacent, equal, and equatorially perpendicular zones, called time zones. Each time zone is delimited by 2 meridians. UTC is the time standard against which all other time zones in the world are referenced.

UTC is measured with astronomical techniques at the Greenwich astronomical observatory in England.

Daylight Saving Time

“Just as sunflowers turn their heads to catch every sunbeam, there is a simple way to get more from the sun.”

Purpose of Daylight Saving Time

100004

The main purpose of daylight saving time (called Summer Time in many places around the world) is to make better use of daylight. By switching clocks an hour forward in summer, we can save a lot of energy and enjoy sunny summer evenings. Today approximately 70 countries use daylight saving time.

When Is Daylight Saving Time Observed Around the World?

Country Begin Daylight Saving Time Back to Standard time
US; Mexico; Canada 2:00 a.m. on the first Sunday of April 2:00 a.m. on the last Sunday of October
European Union 1:00 a.m. on the last Sunday in March 2:00 a.m. on the last Sunday of October

Equatorial and tropical countries from the lower latitudes do not observe daylight saving time. Because the daylight hours are similar during every season, there is no advantage to moving clocks forward during the summer.

How Is This Information Relevant To Time Zones?

The world is divided into 24 time zones and UTC is the time standard against which all other time zones in the world are referenced. When daylight saving time comes into effect in certain countries, the time zone offset for that country is adjusted to accomodate the change in time.

For example: The standard time zone offset for Geneva, Switzerland is UTC +01:00 hour. But when daylight saving time comes into effect the time zone offset changes to UTC +02:00 hours. The time zone offset changes to UTC +01:00 hour again, on the last Sunday in October, when the daylight saving time comes to an end.

Summary

The key learning points in this topic group included:

Coordinated Universal Time:
UTC is the time standard against which all other time zones in the world are referenced.

UTC Conversion:
To convert UTC to local time, you add or subtract hours from it. For regions
west of the zero meridian to the international date line (which includes all of North
America), hours are subtracted from UTC to convert to local time.

Daylight Saving Time:
Daylight saving time is used to make better use of daylight hours by switching clocks an hour forward in summer.

All this information is necessary to understand how the Oracle9i server provides support for time zones in its multi geography applications.

The next topic group “Database Time Zone Versus Session Time Zone” discusses the difference between Database Time Zone and Session Time Zone.

Database Time Zone Versus Session Time Zone

100015

Database Time Zone
Database time zone refers to the time zone in which the database is located.

Session Time Zone
Session time zone refers to the user’s time zone, from where he or she has logged on to the database.

Global Corporation is a finance company with offices around the world. The company head office is located in Barcelona (time zone : +01 hours). The company database is located in New York (time zone : -05 hours). Miguel from Sydney (time zone : +10 hours) has established a connection to the database.

DBTIMEZONE

The DBTIMEZONEfunction returns the value of the database time zone. The default database time zone is the same as the operating system’s time zone.

The return type is a time zone offset (a character type in the format ‘[+ | -]TZH:TZM‘ ) or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or
ALTER DATABASE
statement.

100016

You can set the database’s default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, the default database time zone is the operating system time zone.


SESSIONTIMEZONE

The SESSIONTIMEZONEfunction returns the value of the session’s time zone.

The return type is a time zone offset (a character type in the format ‘[+|-]TZH:TZM’) or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.

Altering the Session Time Zone

How can I change the session time zone?

The session time zone for a session can be changed with an ALTER SESSIONcommand.

Syntax

ALTER SESSION
SET TIME ZONE = ‘[+ |-] hh:mm’;

 

The key learning points in this topic group included:

Database Time Zone:
Database time zone refers to the time zone in which the database is located. You can use the DBTIMEZONE function to query the value of the database time zone.

Session Time Zone:
Session time zone refers to the time zone from which the user has logged on to the database. You can use the SESSIONTIMEZONE function to query the value of the session time zone.

TIMESTAMP

The TIMESTAMP data type is an extension of the DATEdata type.

It stores the year, month, and day of the DATE data type; the hour, minute, and second values; as well as the fractional second value.

Format

TIMESTAMP [(fractional_seconds_precision)]

The fractional_seconds_precision is used to specify the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

Grand Prix Qualifying Run

The line-up position for the Formula 1 Grand Prix is determined by the results of the qualifying run. Because the difference between the finishing times of the various drivers is very close, the finishing time of each driver is measured in fractional seconds. To store this kind of information, you can use the new TIMESTAMP data type.

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE is a variant of the TIMESTAMP data type, that includes a time zone displacementin its value.

Format

TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE

Earthquake Monitoring Station

Earthquake monitoring stations around the world record the details of tremors detected in their respective regions. The date and time of the occurrence of these tremors are stored, along with the time zone displacement, using the new TIMESTAMP WITH TIME ZONE data type. This helps people who analyze the information from locations around the world obtain an accurate perspective of the time when the event occurred.

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE is another variant of the TIMESTAMPdata type. This data type also includes a time zone displacement.

Format

TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE

The TIMESTAMP WITH LOCAL TIME ZONE datatype differs from TIMESTAMP WITH TIME ZONE in that when you insert a value into a database column, the time zone displacement is used to convert the value to the database time zone.

Example

When a New York client inserts TIMESTAMP’1998-1-23 6:00:00-5:00′ into a TIMESTAMP WITH LOCAL TIME ZONE column in the San Francisco database. The inserted data is stored in San Francisco as binary value 1998-1-23 3:00:00.

The time-zone displacement is not stored in the database column.When you retrieve the value, Oracle returns it in your local session time zone.

When the New York client selects that inserted data from the San Francisco database, the value displayed in New York is `1998-1-23 6:00:00′. A San Francisco client, selecting the same data, gets the value ‘1998-1-23 3:00:00’.

New Year Celebration Broadcast

A television company is planning a live broadcast of New Year celebrations across the globe. To schedule a broadcast of the various events from across the globe, they use an application that stores the broadcast time using the TIMESTAMP WITH LOCAL TIME ZONE data type. Reporters located in different time zones can easily query to find out when to start and end their broadcasts, the output of which will be in their respective time zones.

TIMESTAMP:
With the new TIMESTAMP data type you can store the year, month, and day of the DATE data type; hour, minute, and second values; as well as the fractional second value.

TIMESTAMP WITH TIME ZONE:
The TIMESTAMP WITH TIME ZONE data type is a variant of the TIMESTAMP data type, that includes a time zone displacement in its value.

TIMESTAMP WITH LOCAL TIME ZONE:
The data stored in a column of type TIMESTAMP WITH LOCAL TIME ZONE is converted and normalized to the database time zone. Whenever a user queries the column data, Oracle returns the data in the user’s local session time zone.

TZ_OFFSET

Richard, a marketing executive, travels frequently to cities across the globe. He carries his laptop while travelling and updates the database located at the head office in San Francisco with information about his activities at the end of each day.

Since Richard is using a laptop for his work, he needs to update the session time zone every time he visits a new city.

Richard uses the TZ_OFFSET function to find the time zone offset for that city.

Syntax

SELECT TZ_OFFSET(‘Canada/Pacific’) FROM DUAL;

Note: For a listing of valid time zone name values, you can query the V$TIMEZONE_NAMES dynamic performance view.

ALTER SESSION Command

After Richard finds the time zone offset for the city he is visiting, he alters his session time zone using the ALTER SESSION command.

ALTER SESSION
SET TIME_ZONE = ‘-08:00’;

Richard then uses any of the following functions to view the current date and time in the session time zone.

CURRENT_DATE
CURRENT_TIMESTAMP
LOCAL_TIMESTAMP

Note: The following pages contain a detailed explanation of the functions listed above.

CURRENT_DATE

The CURRENT_DATE function returns the current date in the session’s time zone.The return value is a date in the Gregorian calendar. (The ALTER SESSION command can be used to set the date format to ‘DD-MON-YYYY HH24:MI:SS’.)

The CURRENT_DATE function is sensitive to the session time zone.

When Richard alters his session time zone to the time zone of the city that he is visiting, the output of the CURRENT_DATE function changes.

Example

Before the Session Time Zone is Altered

After the Session Time Zone is Altered

Observe in the output that the value of CURRENT_DATE changes when the TIME_ZONE parameter value is changed to -08:00.

Note: The SYSDATE remains the same irrespective of the change in the TIME_ZONE.
SYSDATE is not sensitive to the session’s time zone.

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of the TIMESTAMP WITH TIME ZONE data type.

The time zone displacement reflects the local time zone of the SQL session.

Format

CURRENT_TIMESTAMP (precision)

Where precision is an optional argument that specifies the fractional second precision of the time value returned.

LOCALTIMESTAMP

 

The LOCALTIMESTAMP function returns the current date and time in the session time zone in a value of TIMESTAMP data type.

The difference between this function and the CURRENT_TIMESTAMP function is that LOCALTIMESTAMP returns a TIMESTAMP value, whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Format

LOCALTIMESTAMP (TIMESTAMP_precision)

Where TIMESTAMP_precision is an optional argument that specifies the fractional second precision of the TIMESTAMP value returned.

EXTRACT

So far you have learned how Richard can alter his session date and view the current date and time in the session time zone.

Now observe how Richard can query a specified datetime field from a datetime or interval value expression using the EXTRACT function.

Format

SELECT EXTRACT ([YEAR] [MONTH] [DAY] [HOUR] [MINUTE] [SECOND]  [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);

Using the EXTRACT function, Richard can extract any of the components mentioned in the preceding syntax.

Example

Richard can query the time zone displacement for the current session as follows:

SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP) "Hour",                         
EXTRACT(TIMEZONE_MINUTE FROM CURRENT_TIMESTAMP) "Minute" FROM DUAL;

Datetime Functions: Conversion

Now examine some additional functions that help convert a CHAR value to a TIMESTAMP value, a TIMESTAMP value to a TIMESTAMP WITH TIME ZONEvalue, and so on.

The functions are:

TO_TIMESTAMP
TO_TIMESTAMP_TZ
FROM_TZ

TO_TIMESTAMP
The TO_TIMESTAMP function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMPdata type.

Format

TO_TIMESTAMP(char,[fmt],[‘nlsparam’])

The optional fmt specifies the format of char. If you omit fmt, the string must be in the default format of the TIMESTAMP data type.

The optional nlsparam specifies the language in which month and day names and abbreviations are returned. If you omit nlsparams, this function uses the default date language for your session.

Example

SELECT TO_TIMESTAMP(‘2000-12-01 11:00:00’,
‘YYYY-MM-DD HH:MI:SS’)
FROM DUAL;

TO_TIMESTAMP_TZ

The TO_TIMESTAMP_TZ function converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP WITH TIME ZONEdata type.

Format

TO_TIMESTAMP_TZ
(char,[fmt],[‘nlsparam’])

The optional fmt specifies the format of char. If you omit fmt, the string must be in the default format of the TIMESTAMP data type.

The optional nlsparam specifies the language in which month and day names and abbreviations are returned. If you omit nlsparams, this function uses the default date language for your session.

Example

SELECT TO_TIMESTAMP_TZ(‘2000-12-01 11:00:00 -08:00’,
‘YYYY-MM-DD HH:MI:SS TZH:TZM’)
FROM DUAL;

Note: The TO_TIMESTAMP_TZ function does not convert character strings to TIMESTAMP WITH LOCAL TIME ZONE.

FROM_TZ

The FROM_TZ function converts a timestamp value to a TIMESTAMP WITH TIME ZONEvalue.

Format

FROM_TZ(timestamp_value, time_zone_value)

Time_zone_value can be a character string in the format ‘TZH:TZM’ format or a character expression that returns a string in TZR (time zone region) format with optional TZD (time zone displacement) format.

Example Using the Format TZH:TZM

SELECT from_tz(TIMESTAMP ‘2000-12-01 11:00:00’,
‘-8:00’) “FROM_TZ”
FROM DUAL;

Example Using TZR

SELECT FROM_TZ(TIMESTAMP ‘2000-12-01 11:00:00’, ‘AUSTRALIA/NORTH’) “FROM_TZ”
FROM DUAL;

INTERVAL Data Type

The INTERVALdata type is used to represent the precise difference between two datetime values.

The two INTERVAL data types introduced in Oracle9i are:

INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

Usage of the INTERVAL Datatype

The INTERVAL data type can be used to set a reminder for a time in the future or check whether a certain period of time has elapsed since a particular date.
For example: You can use it to record the time between the start and end of a race.

INTERVAL YEAR TO MONTH

You can use the INTERVAL YEAR TO MONTHdata type to store and manipulate intervals of years and months.

Format

INTERVAL YEAR[(precision)] TO MONTH

Where precision specifies the number of digits in the years field.

You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0-4. The default value is 2.

Automated Generation of Expiration Date

The packaging department of Home Food Products Ltd has decided to automate the generation of the expiration date details of its products.

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECONDstores a period of time in terms of days, hours, minutes, and seconds.

Format

INTERVAL DAY[(day_precision)]
TO SECOND[(fractional_seconds_precision)]

Where day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.

Fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

Automated Generation of the Arrival Time

The Railway Enquiry department wants to automate the generation of the arrival time for all of its trains.

You have just learned about the new INTERVAL data types introduced with the Oracle9iserver.

INTERVAL YEAR TO MONTH:

The data type INTERVAL YEAR TO MONTH is used to store and manipulate intervals of years and months.

TO_YMINTERVAL function:

The TO_YMINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH type, where CHAR is the character string to be converted.

INTERVAL DAY TO SECOND:

The INTERVAL DAY TO SECOND data type stores a period of time in terms of days, hours, minutes, and seconds.

TO_DSINTERVAL function:

The TO_DSINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND data type.

 

沪ICP备14014813号

沪公网安备 31010802001379号