TIMEZONE指的是當地時間與本初子午線英格蘭格林威治時間的時差
北京是東八區(+08:00)，即北京時間-格林威治=8小時，北京比格林威治早8小時看到太陽

SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00 +08:00 2018/4/17 20:05:26.192987 2018/4/17 20:05:26.192987 +08:00 2018/4/17 20:05:26.192983 +08:00
alter session set time_zone='+09:00';
SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00 +09:00 2018/4/17 21:05:02.937731 2018/4/17 21:05:02.937731 +09:00 2018/4/17 20:05:02.937726 +08:00

DBTIMEZONE：返回資料庫時區.
DBTIMEZONE returns the value of the database time zone

SESSIONTIMEZONE：返回當前會話時區
SESSIONTIMEZONE returns the time zone of the current session

LOCALTIMESTAMP：返回session端不帶時區的timestamp格式的當前時間
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP

CURRENT_TIMESTAMP：返回session端帶時區的timestamp格式的當前時間
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE.

SYSTIMESTAMP：返回帶時區的timestamp格式的當前資料庫時間
SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

alter session set time_zone='+08:00';
CREATE TABLE test1 (ID number(2),t_timezone timestamp with time zone,t_local_zone timestamp with local time zone);
insert into test1 values (2,systimestamp,systimestamp);
select * from test1;
2 2018/4/17 20:09:03.298221 +08:00 2018/4/17 20:09:03.298221
alter session set time_zone='+09:00';
select * from test1;
2 2018/4/17 20:09:03.298221 +08:00 2018/4/17 21:09:03.298221

總結：利用timestamp時間欄位屬性TIMESTAMP WITH TIME ZONE，可以把資料庫伺服器所在的時間轉化為當前時區的時間，比如當倫敦時間為20180101 02:00:00，則在北京可以看到該時間為20180101 09:00:00。

TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed.
TZ_OFFSET根據輸入的引數值，返回時區與0時區相差的小時和分鐘數。
SELECT TZ_OFFSET('Asia/Shanghai'),TZ_OFFSET('US/Michigan'),TZ_OFFSET('Europe/London') FROM DUAL;
+08:00 -04:00 +01:00
select TZNAME from V$TIMEZONE_NAMES--查詢所有的time_zone_name

FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.
FROM_TZ將時間戳值和時區轉換為具有時區值的時間戳。
SELECT FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','Asia/Shanghai'),FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','US/Michigan'),FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','Europe/London') FROM DUAL;
2018/4/30 8:00:00.000000000 +08:00 2018/4/30 8:00:00.000000000 -04:00 2018/4/30 8:00:00.000000000 +01:00
SELECT FROM_TZ(LOCALTIMESTAMP,'Asia/Shanghai'),FROM_TZ(LOCALTIMESTAMP,'US/Michigan'),FROM_TZ(LOCALTIMESTAMP,'Europe/London') FROM DUAL;
2018/4/17 20:14:47.519347 +08:00 2018/4/17 20:14:47.519347 -04:00 2018/4/17 20:14:47.519347 +01:00
SELECT FROM_TZ(SYSTIMESTAMP,'Asia/Shanghai'),FROM_TZ(SYSTIMESTAMP,'US/Michigan'),FROM_TZ(SYSTIMESTAMP,'Europe/London') FROM DUAL;--因為SYSTIMESTAMP本身帶有時區，所以報錯ORA-00932: 資料型別不一致: 應為 TIMESTAMP, 但卻獲得 TIMESTAMP WITH TIME ZONE。

TIMESTAMP和TIMESTAMP WITH TIME ZONE之間的總結

相關文章