Oracle 資料庫中的 時間 時區

本節，我會引入很多概念，慢慢的一個一個的消化。。。。。。。

CET

歐洲中部時間（英語：Central European Time，CET）是比世界标準時間（UTC）早一個小時的時區名稱之一。它被大部分歐洲國家和部分北非國家采用。冬季時間為UTC+1，夏季歐洲夏令時為UTC+2。

參考：http://zh.wikipedia.org/zh/%E6%AC%A7%E6%B4%B2%E4%B8%AD%E9%83%A8%E6%97%B6%E9%97%B4

CST

中原標準時間，China Standard Time，又名中國标準時間，是中國的标準時間。在時區劃分上，屬東八區，比協調世界時早8小時，記為UTC+8，與中華民國國家标準時間（舊稱“中原标準時間”）、香港時間和澳門時間和相同。當格林威治時間為淩晨0:00時，中國標準時間剛好為上午8:00。

參考：http://zh.wikipedia.org/zh/CSThttp://zh.wikipedia.org/wiki/%E5%8C%97%E4%BA%AC%E6%97%B6%E9%97%B4

關系

CET=UTC/GMT + 1小時

CST=UTC/GMT +8 小時

CST=CET+9

DST夏令時：

DST是Daylight Saving Time的縮寫，稱為陽光節約時，在我國稱為夏時制，又稱夏令時，是一種為節約能源而人為調整地方時間的制度。有些國家DST的使用時間較長，（如美國長達7個月）跨越了春夏秋等三個季節，是以簡單地用夏時制的概念已經不能完全表達DST的确切含義了，是以有人也稱其為節能時。本文統一使用“DST”這一說法。所謂的DST，就是利用夏季天亮得早這一自然現象，人為地将時間提前一小時。這樣就可以使人們早起早睡，以充分利用光照資源，減少照明時間，進而節約照明用電。

timezone offset 和 named timezone：

所謂offset就是指偏移，位移，比如我們東八區就會用 UTD/GMT +8 這個偏移量來表示；而named表示方法一般是這樣：(example: Canada/Eastern ，Asia/Shanghai or GMT) ，直接用地區來表示這個timezone。那麼既然有了這種offset的表示方法，何必再有用named的表示方法呢？原因就是因為上文提到的DST夏令時，有些非常注重環保節約的國家，會在夏季統一把時鐘調快一個小時，比如倫敦冬天時UTC/GMT+0，但是到了夏天就成了UTC/GMT+1了。這樣，偏移量表示就沒法了，到底用哪個還得根據不同季節來回變，但是這個named timezone就好啦，不管怎麼樣，我就是倫敦timezone。

DATABASE timezone：參考文檔：Timestamps & time zones - Frequently Asked Questions (文檔 ID 340512.1)

資料庫的時區并不像聽起來那麼重要。首先，它不影響像SYSDATE，或SYSTIMESTAMP功能，這些功能把他們的内容（日期和時間，并在SYSTIMESTAMP也時區的情況下）完全取決于作業系統。沒有任何“oracle”産品的影響。

資料庫時區的唯一功能是：它作為一個時區标準，“TIMESTAMP WITH LOCAL TIME ZONE”（TSLTZ）資料類型的值都被格式化并存儲成目前資料庫時區的格式。然而，在回話的檢索或者插入式，這些存儲的值将被轉換成目前會話端的時區，是以資料庫時區的實際設定是或多或少的不重要。

用于會話的時區是會話的時區，并在用戶端的定義，而不是資料庫端

該DBTIMEZONE應設定為一個偏移量（+00:00，-05:00或+09:00例如），或不會受DST（如UTC或GMT）靜态時區，而不是一個命名時區是由DST（像歐洲/布魯塞爾或美國/中部）的影響。

如何檢視資料庫目前的timezone呢？可以用如下指令：

select dbtimezone from dual;

這個dbtimezone隻是用來格式化TSLTZ的資料，沒有任何别的用處，會在資料庫建立的時候有個sql（ CREATE DATABASE... SET TIME_ZONE='+00:00';）可以去指定，如果不指定，就會用os server上的timezone offset。

After database creation the ALTER DATABASE SET TIME_ZONE statement can be used to change the database time zone. This will only work if there are no TSLTZ values already stored in the database or an  ORA-02231 (9i) or ORA-30079 will be seen:

<code>SQL&gt; ALTER DATABASE SET TIME_ZONE = '+00:00';</code>

The change will not take effect until the database is restarted.

You can only change the database time zone if you have no TIMESTAMP WITH LOCAL TIME ZONE columns in the database otherwise ORA-02231: missing or invalid option to ALTER DATABASE (in 9i) or ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns (in 10g and up) will be seen.  

To change the DBTIMEZONE for an database that has already TIMESTAMP WITH LOCAL TIME ZONE columns one need to

a) export all tables that have TIMESTAMP WITH LOCAL TIME ZONE columns

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col

from dba_tab_cols c, dba_objects o

where c.data_type like '%WITH LOCAL TIME ZONE'

and c.owner=o.owner

and c.table_name = o.object_name

and o.object_type = 'TABLE'

order by col

/

b) truncate or drop those tables

c) change the DBTIMEZONE making sure the are no tables with TSLTZ in the RECYCLEBIN and restart the database

<code>SQL&gt;PURGE DBA_RECYCLEBIN / SQL&gt; ALTER DATABASE SET TIME_ZONE = '+00:00'; SQL&gt; SHUTDOWN</code>

 d) import the exported table again.

They all depend on the session timezone, which is defined on the CLIENT side, not server side.

CURRENT_DATE returns the current date and time in the session time zone in a value of datatype DATE. 

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. 

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.

The sessions NLS_DATE_FORMAT defines the output format of a DATE, NLS_TIMESTAMP_FORMAT defines the output format of a TIMESTAMP, the NLS_TIMESTAMP_TZ_FORMAT defines the output format of a TIMESTAMP WITH TIME ZONE.

ALTER SESSION SET TIME_ZONE = '-05:00';

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD' NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS' NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';

SELECT SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

-- gives for example:

SQL&gt; SELECT SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FRO

M DUAL;

SESSIONTIMEZONE

---------------------------------------------------------------------------

CURRENT_DATE

-------------------

CURRENT_TIMESTAMP

LOCALTIMESTAMP

-05:00

28-10-2009 06:47:19

28/10/2009 06:47:18 -05:00

28*10*2009 06:47:18

-- and when doing an alter of the session timezone

ALTER SESSION SET TIME_ZONE = '-08:00';

-- gives for example:

-08:00

28-10-2009 03:47:49

28/10/2009 03:47:49 -08:00

28*10*2009 03:47:49

2、概述

商業和資料庫很多時候必須跨時區工作，從9i開始，oracle環境開始有了時區意識，通過指定資料庫的時區和使用如下兩種資料類型來存儲時區：

TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE。

TIMESTAMP WITH TIME ZONE（TSTZ）：，存儲了時間和真實的時區（用offset/name的形式表示），存儲了現在的時間，時區是東八區。但是不會存儲資料庫的時區。這個資料類型的格式是由資料庫參數NLS_TIMESTAMP_TZ_FORMAT來決定的，如下：

nls_timestamp_tz_format     string DD-MON-RR HH.MI.SSXFF AM TZR

當涉及到時區的時候，TSTZ通常是最好的存儲時區資訊的資料類型。通過下面這個查詢，會查出你資料庫中所有TSTZ的列：

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'

    || c.data_type || ' ' col

  from dba_tab_cols c, dba_objects o

 where c.data_type like '%WITH TIME ZONE'

    and c.owner=o.owner

   and c.table_name = o.object_name

   and o.object_type = 'TABLE'

order by col

TIMESTAMP WITH LOCAL TIME ZONE（TSLTZ）：會同時存儲資料庫時區，并且會根據查詢用戶端的時區進行相應的轉換。

資料庫時區可以在建立時在create database指令中設定，也可以使用alter database set TIME_ZONE=...來修改。如果沒有特别的指定，預設的，資料庫将遵從主機作業系統時區設定，或者取自環境變量ORA_STDZ。所有支援的時區記錄在V$TIMEZONE_NAMES動态性能表中。時區有三種表示方法，全名、縮寫和相對于标準時間（格林威治時間）的固定偏移,比如标準時間相應的三種表示方法分别為：Etc/Greenwich、GMT和+00:00。

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'   || c.data_type || ' ' col

 where c.data_type like '%WITH LOCAL TIME ZONE'

Oracle 9i 開始多了 3 個關于時間的資料類型：TIMESTAMP [(precision)] TIMESTAMP [(precision)] WITH TIME ZONE TIMESTAMP [(precision)] WITH LOCAL TIME ZONE，其中 TIMESTAMP [(precision)] WITH TIME ZONE 儲存了時區資訊。