ORACLE 時間與時區（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

--會話時區
select sessiontimezone from dual;
---數據庫時區
select systimestamp from dual;
--time with zone
select dbtimezone from dual;

前者表示數據庫時區，后者表示客戶端時區。只有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時未給出時區信息，那么默認為當前客戶端的時區。

四）

一些與時區相關的函數：

五）

時區的基本換算

時區分東西，東時區都帶+號標識，西時區用-號。在一個特定的時間點，時區號越大（考慮正負號）的地方，時間越晚，比如北京在+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小時）。