TIMESTAMP的時區轉換

TIMESTAMP既然包括了時區的概念，就會碰到將一個時區的時間轉換到另一個數區的問題。

 

 

在時區轉換之前，首先描述一下如何指定一個包含時區的TIMESTAMP，常用方法有以下幾種：

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-------------------------------------------------------------------
09-3月 -09 05.37.29.468000 下午 +08:00

SQL> SELECT SYSTIMESTAMP + INTERVAL '5' HOUR FROM DUAL;

SYSTIMESTAMP+INTERVAL'5'HOUR
-------------------------------------------------------------------
09-3月 -09 10.38.02.000000000 下午 +08:00

對於伺服器當前時區的時間，可以透過SYSTIMESTAMP加減INTERVAL的方式實現。

第二種方法使用TO_TIMESTAMP_TZ：

SQL> SELECT TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00',
  2  'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
  3  FROM DUAL;

TO_TIMESTAMP_TZ('2009-3-917:51:23.23-05:00','YYYY-MM-DDHH24:MI:SS.FFTZH:TZM
---------------------------------------------------------------------------
09-3月 -09 05.51.23.230000000 下午 -05:00

Oracle還提供一個FROM_TZ的函式，用來轉換一個TIMESTAMP型別到TIMESTAMP WITH TIME ZONE型別：

SQL> SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), '-11:00')
  2  FROM DUAL;

FROM_TZ(CAST(SYSDATEASTIMESTAMP),'-11:00')
--------------------------------------------------------------
09-3月 -09 05.44.18.000000 下午 -11:00

當然還可以透過TIMESTAMP加字串的方式來直接指定一個包含時區的TIMESTAMP型別：

SQL> SELECT TIMESTAMP '2009-3-9 17:56:53.232323 +10:00'
  2  FROM DUAL;

TIMESTAMP'2009-3-917:56:53.232323+10:00'
----------------------------------------------------------
09-3月 -09 05.56.53.232323000 下午 +10:00

最後可以使用TIMESTAMP AT TIME ZONE的方式指定時區：

SQL> SELECT TIMESTAMP '2009-3-9 17:56:53.232323'
  2  AT TIME ZONE 'GMT'
  3  FROM DUAL;

TIMESTAMP'2009-3-917:56:53.232323'ATTIMEZONE'GMT'
-----------------------------------------------------
09-3月 -09 09.56.53.232323000 上午 GMT

SQL> SELECT TO_CHAR(
  2  TIMESTAMP '2009-3-9 17:56:53.232323'
  3  AT TIME ZONE 'GMT'
  4  , 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
  5  FROM DUAL;

TO_CHAR(TIMESTAMP'2009-3-917:56:53.2
------------------------------------
2009-03-09 09:56:53.232323000 +00:00

而Oracle的最後這種方式，不僅支援時區名稱方式，而且支援時區的轉換。

比如希望將東4區時間轉換為西3區的時間：

SQL> SELECT TIMESTAMP '2009-3-9 18:05:23.234 +04:00'
  2  AT TIME ZONE '-03:00'
  3  FROM DUAL;

TIMESTAMP'2009-3-918:05:23.234+04:00'ATTIMEZONE'-03:00'
---------------------------------------------------------------------
09-3月 -09 11.05.23.234000000 上午 -03:00

SQL> SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'Asia/Shanghai')
  2  AT TIME ZONE 'GMT'
  3  FROM DUAL;

FROM_TZ(CAST(SYSDATEASTIMESTAMP),'ASIA/SHANGHAI')ATTIMEZONE'GMT'
---------------------------------------------------------------------
09-3月 -09 09.54.10.000000 上午 GMT