ORACLE HANDBOOK系列之九：时间与时区（Time and Time Zone）

一）

 

Oracle中的四种时间类型

Date

Timestamp

Timestamp with local time zone

Timestamp with time zone

这四种类型中，前两个与时区完全无关，它们的“行为”就像varchar2或者number类型一样，就是你插入时是什么值，那么存储的也是一样的值，同时查询出来的也是一样的值（包括你在.NET环境下用ADO.NET或者ODP.NET进行查询），不存在任何所谓的“参数”设置可以改变它们（当然，你可以改变它们的显示格式，但不是值）。

后两个类型则跟时区信息紧密相关，但它们之间又有较大的不同。timestamp with local time zone事实上并不存储时区信息，在向此类型的列中插入值时，用户提供时区信息，但是Oracle会自动将其转换成dbtimezone下的时间进行存储，所以，timestamp with local time zone有时区信息，即数据库时区，但它并不存储时区信息。当查询发生时，Oracle再将时间转化到客户端的时区（sessiontimezone）进行显示。

不同于timestamp with local time zone这种把时区的转换委托给Oracle服务器，timestamp with time zone则是单纯地把你在insert时提供的时间+时区信息保存到数据库中。

 

二）

两个与时区相关的参数

Dbtimezone

Sessiontimezone

前者表示数据库时区，后者表示客户端时区。只有timestamp with local time zone才会受时区修改的影响。这两个参数都可以通过alter命令进行修改。

对客户端操作系统时区的修改将会影响此机器上的oracle客户端的sessiontimezone；修改服务器操作系统时区并不会改变dbtimezone。

SQL> select dbtimezone from dual;
DBTIMEZONE
------------------------------
-06:00


SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
------------------------------
+08:00

修改客户端或数据库时区的alter命令：

alter session set time_zone='+10:00'; 
alter database set time_zone='+10:00'; 

如果数据库中存在有timestamp with local time zone类型的 字段，则在修改dbtimezone时会出现ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

 

三）

如何向数据库插入一个timestamp with time zone或者timestamp with local time zone类型

SQL> create table tz1(twtz timestamp with time zone, twltz timestamp with local time zone);

SQL> insert into tz1 values(timestamp'2011-01-03 15:00:00.000000 +05:00',timestamp'2011-01-03 15:00:00.000000 +05:00');

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> select * from tz1;
TWTZ                                    TWLTZ
---------------------------------------------------------------------------
03-JAN-11 03.00.00.000000 PM +05:00     03-JAN-11 06.00.00.000000 PM

SQL> alter session set time_zone='-06:00';

SQL> select * from tz1;
TWTZ                                    TWLTZ
---------------------------------------------------------------------------
03-JAN-11 03.00.00.000000 PM +05:00     03-JAN-11 04.00.00.000000 AM

除了采用类似“+05:00”这种形式的时区标识外，还可以使用时区的缩写，比如GMT,PST等等（可以查看系统视图 V$TIMEZONE_NAMES）。如果在insert时未给出时区信息，那么默认为当前客户端的时区。

 

四）

一些与时区相关的函数：

函数	返回值	返回值类型
SYSTIMESTAMP	Current date/time, in Database TZ	TIMESTAMP WITH TIME ZONE
CURRENT_TIMESTAMP	Current date/time, in Client Session TZ	TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP	Local date/time in Client Session, but with no TZ info	TIMESTAMP
DBTIMEZONE	Database time zone, in HH:MI offset from GMT	VARCHAR2
SESSIONTIMEZONE	Session time zone, in HH:MI offset from GMT	VARCHAR2
EXTRACT (part FROM date_time)	Extracts year, hour, seconds, time zone name, etc. from a supplied datetime or interval expression.	VARCHAR2
SYS_EXTRACT_UTC(date_time with TZ)	GMT (UTC) time of date/time supplied	TIMESTAMP
TZ_OFFSET(TZ)	Returns hour/minute offset from GMT of TZ	VARCHAR2
FROM_TZ(timestamp,TZ)	Converts a TIMESTAMP to TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE
TO_TIMESTAMP	Convert char + fmt model to TIMESTAMP	TIMESTAMP
TO_TIMESTAMP_TZ	Convert char + fmt model to TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE

 

五）

时区的基本换算

时区分东西，东时区都带+号标识，西时区用-号。在一个特定的时间点，时区号越大（考虑正负号）的地方，时间越晚，比如北京在+08:00区, St. Louis在-06:00区，北京已经是傍晚了，St. Louis还在凌晨，它们之前相差即+08:00-(-06:00)=14（受夏时制的影响，可能会有一个小时的误差）。

 

六）

应该选择哪种时间类型？

如果需要记录的时间精度超过秒，选择timestamp类型。

如果需要将时间在数据库时区与客户端时区之间进行自动转换，选择timestamp with local time zone。

如果需要记录客户插入的时区信息，选择timestamp with time zone。

 

七）

ODP.NET与OracleGlobalization

对于timestamp with time zone类型，由于包含了原始时区信息，所以应用程序中对其进行手动的转换也不困难。除了手动方法，我们还可以通过设置OracleGlobalization下的相关属性让ODP.NET为你进行自动转换。看一个例子：

conn.Open();//connection should be opened before SetSessionInfo() could be invoked.
//
OracleGlobalization og = OracleGlobalization.GetClientInfo();
og.TimeZone = "America/Chicago";
OracleGlobalization.SetThreadInfo(og);
conn.SetSessionInfo(og);
//
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select twtz from tz1";
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
    while (dr.Read())
    {
        OracleTimeStampTZ otstz = dr.GetOracleTimeStampTZ(dr.GetOrdinal("twtz"));
        Console.WriteLine("twtz: " + otstz.ToString());
    }
}
dr.Close();
//
//an alias is necessary when using 'AT LOCAL' predicate
cmd.CommandText = "select twtz AT LOCAL as twtz from tz1";
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
    while (dr.Read())
    {
        OracleTimeStampTZ otstz = dr.GetOracleTimeStampTZ(dr.GetOrdinal("twtz"));
        Console.WriteLine("twtz AT LOCAL: " + otstz.ToString());
    }
}
dr.Close();
 

//output:
twtz:                   03-JAN-11 03.00.00.000000 PM +05:00

twtz AT LOCAL:                        03-JAN-11 04.00.00.000000 AM AMERICA/CHICAGO 

可以看到当设置了时区，并在sql语句中使用了‘AT LOCAL’后，原来的时间被自动转换到了芝加哥的时间（芝加哥在西6区，所以与原时间的时区东5区差为11小时）。