ORACLE 時間與時區（Time and Time Zone）

轉：

一）

中的四種時間型別

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時未給出時區資訊，那麼預設為當前客戶端的時區。

四）

一些與時區相關的函式：

函式	返回值	返回值型別
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小時）。