Oracle的時區問題

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

1. Oracle 的時區設定

Oracle 的時區可以分為兩種，一種是資料庫的時區，一種是 session 時區，也就是客戶端連線時的時區（經過實驗，連線以後再修改客戶端的時區，session 的時區不會更改）。

資料庫的時區在建立資料庫時可以透過在 create database 語句中加上 SET TIME_ZONE = ' { { + | - } hh : mi | time_zone_region } ' 來指定，如果，不指定，預設是按照資料庫所在的作業系統時區來設定的。建立之後，可以透過 alter database 來修改。其中 time_zone_region 引數可以透過查詢 V$TIMEZONE_NAMES 動態檢視來獲得所有支援的值。修改之後，需要重啟資料庫才能生效。經常有人會碰到無法修改的情況：

SQL> alter database set time_zone='+06:00';
alter database set time_zone='+06:00'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

TOM 對此問題有過，TIME_ZONE 的設定主要是為了 WITH LOCAL TIME ZONE，當 session 的時區和資料庫的時區不同時，oracle 根據時區的差距轉換到資料庫的時間，再儲存到資料庫的 WITH LOCAL TIME ZONE 型別中，他是不儲存時區的，所以需要 TIME_ZONE 來進行各種時區之間時間的轉換（WITH TIME ZONE 型別儲存了原始的時區，所以不需要 TIME_ZONE 的設定也可以進行各種時區之間的轉換）。但資料庫中一旦有了該型別，就不能透過 alter database 修改時區了，會得到上面的錯誤，可以透過下面的語句獲得所有包含該型別的表，將他們刪除之後，再修改。

select || '.' || || '.' || TSLTZcolumn
from sys.obj$ o, sys.col$ c, sys.user$ u
where c.type# = 231
and o.obj# = c.obj#
and u.user# = o.owner#;

(一般查詢後的結果為：OE.ORDERS.ORDER_DATE，指的是OE使用者下的ORDERS表的ORDER_DATE欄位使用了時區的資訊：WITH LOCAL TIME ZONE，將此資訊去掉就可以再修改了，修改好了之後需要重啟資料庫才能生效)

Session 的時區是根據客戶端的時區來決定的，當然連線以後也可以透過 alter session 來改變。WITH LOCAL TIME ZONE 型別會根據 TIME_ZONE 的設定，自動把時間轉換為 session 所在時區的時間顯示出來，而 WITH TIME ZONE 因為儲存了時區，不需要根據 TIME_ZONE 的設定來轉換。

2. 檢視時區

可以分別使用 SESSIONTIMEZONE / DBTIMEZONE 內建函式檢視 session 和資料庫時區：

SYS@SKYDB> select dbtimezone from dual;

DBTIME
------
+08:00

SYS@SKYDB> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------
+09:00

另外可以用 TZ_OFFSET 查詢某時區和 UTC 之間的差值。

TZ_OFFSET ( { 'time_zone_name'
| '{ + | - } hh : mi'
| SESSIONTIMEZONE
| DBTMEZONE }
)

SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

TZ_OFFS
-------
-04:00

SELECT TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFSET(DBTI
--------------
+08:00

其中 time_zone_name 也可以從 V$TIMEZONE_NAMES 獲得。

3. 幾個內建時間函式的比較

sysdate/systimestamp 都是返回資料庫的時間並且使用資料庫的時區，他們返回的是作業系統的時間。sysdate 返回的是 date 型別，沒有時區資訊，作業系統上是什麼時間就返回什麼時間；systimestamp 返回 TIMESTAMP WITH TIME ZONE 類新，有時區資訊：

SYS@SKYDB> select sysdate from dual;

SYSDATE
-------------------
2006-08-03 10:01:31

SYS@SKYDB> select systimestamp from dual;

SYSTIMESTAMP
-----------------------------------------------
03-AUG-06 10.02.21.093000 AM +08:00

SYS@SKYDB> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

修改作業系統時區為 +02:00

SYS@SKYDB> startup
ORACLE instance started.

Total System Global Area 89202456 bytes
Fixed Size 454424 bytes
Variable Size 62914560 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SYS@SKYDB> select sysdate from dual;

SYSDATE
-------------------
2006-08-03 04:03:37

SYS@SKYDB> select systimestamp from dual;

SYSTIMESTAMP
----------------------------------------------
03-AUG-06 04.04.15.687000 AM +02:00

注：這是我單位機子上實驗的結果，由於建了多個資料庫，不知道為什麼不能透過 ipc 來連線本地資料了，登陸時使用 sqlplus "as sysdba"，也就是使用了監聽器來連線，但在家裡做相同的實驗，透過 ipc 連線 sqlplus "/as sysdba"，修改時區後，sysdate 依然顯示修改前的時間，而 systimestamp 卻正確，不知道是什麼原因：

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-02-08 22:21:40

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 10.22.38.578000 PM +08:00

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
修改時區為 +09:00
SQL> startup
ORACLE instance started.

Total System Global Area 131145064
bytes

Fixed Size 453992
bytes

Variable Size 109051904
bytes

Database Buffers 20971520
bytes

Redo Buffers 667648

bytes

Database mounted.
Database opened.
SQL> select sysdate from dual;

SYSDATE
---------
02-AUG-06

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-08-02 22:32:59
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 11.35.05.171000 PM +09:00

另外，有個初始化引數 fixed_date，可以設定 sysdate 返回指定的時間：

alter system set fixed_date='2005-04-04-11-00-00'

this fixed_date is normally used, in oracle, for dubugging purpose.

once finishing it, you can set it back:

alter system set fixed_date=none

Eygle 的關於這個引數的相關文章： current_timestamp/current_date 也會返回資料庫的時間，但轉換為 session 的時區進行顯示，可以使用 alter session set time_zone 改變 session 時區。

4. 四個日期時間型別的實驗

SQL> select dbtimezone from dual;

DBTIME
------
+06:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> ed
Wrote file afiedt.buf

1 create table tztest(a date,
2 b timestamp(0),
3 c timestamp(0) with time zone,
4* d timestamp(0) with local time zone)
SQL> /

Table created.

SQL> alter session set nls_date_format ='yyyy-dd-mm hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-02-08 22:21:40

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 10.22.38.578000 PM +08:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2006-02-08 22:23:50

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 10.24.04.031000 PM +08:00

SQL> insert into tztest
2 values(sysdate,systimestamp,systimestamp,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
2006-02-08 22:25:59
02-AUG-06 10.25.59 PM
02-AUG-06 10.25.59 PM +08:00
02-AUG-06 10.25.59 PM

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

修改了客戶端作業系統的時區

C:Documents and SettingsAdministrator>sqlplus sky/xxxx

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Aug 2 23:28:01 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2006-08-02 22:28:49
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02-AUG-06 11.29.33.609000 PM +09:00
SQL> select * from tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
2006-08-02 22:25:59 02-AUG-06 10.25.59 PM 02-AUG-06 10.25.59 PM +08:00 02-AUG-06 11.25.59 PM

[@more@]

轉：Oracle TimeZone

Oracle的時區問題

相關文章