朱杰兵blog

Oracle中的四種時(shí)間類型

Date

Timestamp

Timestamp with local time zone

Timestamp with time zone

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

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

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

二）

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

Dbtimezone

Sessiontimezone

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

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

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


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

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

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

三）

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

四）

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

五）

時(shí)區(qū)的基本換算

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

例如：

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

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

--insert數(shù)據(jù)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 = 時(shí)區(qū)小時(shí),tzm = 時(shí)區(qū)分鐘

結(jié)果

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 下午

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