ORACLE 時間與時區(qū)（Time and Time Zone）

一）

Oracle中的四種時間類型

Date

Timestamp

Timestamp with local time zone

Timestamp with time zone

這四種類型中，前兩個與時區(qū)完全無關(guān)，它們的“行為”就像varchar2或者number類型一樣，就是你插入時是什么值，那么存儲的也是一樣的值，同時查詢出來的也是一樣的值（包括你在.NET環(huán)境下用ADO.NET或者ODP.NET進行查詢），不存在任何所謂的“參數(shù)”設(shè)置可以改變它們（當然，你可以改變它們的顯示格式，但不是值）。

后兩個類型則跟時區(qū)信息緊密相關(guān)，但它們之間又有較大的不同。timestamp with local time zone事實上并不存儲時區(qū)信息，在向此類型的列中插入值時，用戶提供時區(qū)信息，但是Oracle會自動將其轉(zhuǎn)換成dbtimezone下的時間進行存儲，所以，timestamp with local time zone有時區(qū)信息，即數(shù)據(jù)庫時區(qū)，但它并不存儲時區(qū)信息。當查詢發(fā)生時，Oracle再將時間轉(zhuǎn)化到客戶端的時區(qū)（sessiontimezone）進行顯示。

不同于timestamp with local time zone這種把時區(qū)的轉(zhuǎn)換委托給Oracle服務(wù)器，timestamp with time zone則是單純地把你在insert時提供的時間+時區(qū)信息保存到數(shù)據(jù)庫中。

二）

兩個與時區(qū)相關(guān)的參數(shù)

Dbtimezone

Sessiontimezone

--會話時區(qū)
select sessiontimezone from dual;
---數(shù)據(jù)庫時區(qū)
select systimestamp from dual;
--time with zone
select dbtimezone from dual;

前者表示數(shù)據(jù)庫時區(qū)，后者表示客戶端時區(qū)。只有timestamp with local time zone才會受時區(qū)修改的影響。這兩個參數(shù)都可以通過alter命令進行修改。

對客戶端操作系統(tǒng)時區(qū)的修改將會影響此機器上的oracle客戶端的sessiontimezone；修改服務(wù)器操作系統(tǒng)時區(qū)并不會改變dbtimezone。

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


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

修改客戶端或數(shù)據(jù)庫時區(qū)的alter命令：

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

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

如果數(shù)據(jù)庫中存在有timestamp with local time zone類型的 字段，則在修改dbtimezone時會出現(xiàn)ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

三）

如何向數(shù)據(jù)庫插入一個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”這種形式的時區(qū)標識外，還可以使用時區(qū)的縮寫，比如GMT,PST等等（可以查看系統(tǒng)視圖 V$TIMEZONE_NAMES）。如果在insert時未給出時區(qū)信息，那么默認為當前客戶端的時區(qū)。

四）

一些與時區(qū)相關(guān)的函數(shù)：

五）

時區(qū)的基本換算

時區(qū)分東西，東時區(qū)都帶+號標識，西時區(qū)用-號。在一個特定的時間點，時區(qū)號越大（考慮正負號）的地方，時間越晚，比如北京在+08:00區(qū), St. Louis在-06:00區(qū)，北京已經(jīng)是傍晚了，St. Louis還在凌晨，它們之前相差即+08:00-(-06:00)=14（受夏時制的影響，可能會有一個小時的誤差）。

六）

應(yīng)該選擇哪種時間類型？

如果需要記錄的時間精度超過秒，選擇timestamp類型。

如果需要將時間在數(shù)據(jù)庫時區(qū)與客戶端時區(qū)之間進行自動轉(zhuǎn)換，選擇timestamp with local time zone。

如果需要記錄客戶插入的時區(qū)信息，選擇timestamp with time zone。

七）

ODP.NET與OracleGlobalization

對于timestamp with time zone類型，由于包含了原始時區(qū)信息，所以應(yīng)用程序中對其進行手動的轉(zhuǎn)換也不困難。除了手動方法，我們還可以通過設(shè)置OracleGlobalization下的相關(guān)屬性讓ODP.NET為你進行自動轉(zhuǎn)換?？匆粋€例子：

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

可以看到當設(shè)置了時區(qū)，并在sql語句中使用了‘AT LOCAL’后，原來的時間被自動轉(zhuǎn)換到了芝加哥的時間（芝加哥在西6區(qū)，所以與原時間的時區(qū)東5區(qū)差為11小時）。