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:00twtz AT LOCAL:                        03-JAN-11 04.00.00.000000 AM AMERICA/CHICAGO 

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

 

 

 

 

 

 

 

标签: oracle, time zone, timestamp, OracleGlobalization绿色通道:好文要顶关注我收藏该文与我联系


分享给朋友:
您可能感兴趣的文章:
随机阅读: