朱杰兵blog

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命令：

如果數據庫中存在有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（受夏時制的影響，可能會有一個小時的誤差）。

例如：

create table d (dt date,dt_timestamp timestamp,dt_timestamp_tz timestamp with time zone,dt_timestamp_ltz timestamp with local time zone)

--insert數據1
insert into d values (sysdate,sysdate,sysdate,sysdate)

--insert數據2

insert into d values (
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'),
to_timestamp_tz('2013-07-31 01:00:00.1234 -5:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm')
)

tzh = 時區小時,tzm = 時區分鐘

結果

1 2013-07-31 18:31:37 31-7月 -13 06.31.37.000000 下午 31-7月 -13 06.31.37.000000 下午 +08:00 31-7月 -13 06.31.37.000000 下午
2 2013-07-31 01:00:00 31-7月 -13 01.00.00.123400 上午 31-7月 -13 01.00.00.123400 上午 -05:00 31-7月 -13 02.00.00.123400 下午

紅色計算公式:sessiontimezone(+8) - (-5:00) = 13小時,所以01:00:00+13=14點，即02.00.00.123400 下午