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 u.name || '.' || o.name || '.' || c.name 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 "/@skydb 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 的關於這個參數的相關文章：Why sysdate is fixed

    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 Settings\Administrator>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                  <-自動轉換為 session 的時區