TIMESTAMP和TIMESTAMP WITH TIME ZONE之間的總結(jié)

發(fā)布時(shí)間：2020-08-10 00:25:32 來源：ITPUB博客閱讀：357 作者：lusklusklusk 欄目：關(guān)系型數(shù)據(jù)庫

TIMEZONE指的是當(dāng)?shù)貢r(shí)間與本初子午線英格蘭格林威治時(shí)間的時(shí)差
北京是東八區(qū)(+08:00)，即北京時(shí)間-格林威治=8小時(shí)，北京比格林威治早8小時(shí)看到太陽

SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00+08:002018/4/17 20:05:26.1929872018/4/17 20:05:26.192987 +08:002018/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:002018/4/17 21:05:02.9377312018/4/17 21:05:02.937731 +09:002018/4/17 20:05:02.937726 +08:00

DBTIMEZONE：返回?cái)?shù)據(jù)庫時(shí)區(qū).
DBTIMEZONE returns the value of the database time zone

SESSIONTIMEZONE：返回當(dāng)前會(huì)話時(shí)區(qū)
SESSIONTIMEZONE returns the time zone of the current session

LOCALTIMESTAMP：返回session端不帶時(shí)區(qū)的timestamp格式的當(dāng)前時(shí)間
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP

CURRENT_TIMESTAMP：返回session端帶時(shí)區(qū)的timestamp格式的當(dāng)前時(shí)間
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE.

SYSTIMESTAMP：返回帶時(shí)區(qū)的timestamp格式的當(dāng)前數(shù)據(jù)庫時(shí)間
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;
22018/4/17 20:09:03.298221 +08:002018/4/17 20:09:03.298221
alter session set time_zone='+09:00';
select * from test1;
22018/4/17 20:09:03.298221 +08:002018/4/17 21:09:03.298221

總結(jié)：利用timestamp時(shí)間字段屬性TIMESTAMP WITH TIME ZONE，可以把數(shù)據(jù)庫服務(wù)器所在的時(shí)間轉(zhuǎn)化為當(dāng)前時(shí)區(qū)的時(shí)間，比如當(dāng)倫敦時(shí)間為20180101 02:00:00，則在北京可以看到該時(shí)間為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根據(jù)輸入的參數(shù)值，返回時(shí)區(qū)與0時(shí)區(qū)相差的小時(shí)和分鐘數(shù)。
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將時(shí)間戳值和時(shí)區(qū)轉(zhuǎn)換為具有時(shí)區(qū)值的時(shí)間戳。
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:002018/4/30 8:00:00.000000000 -04:002018/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:002018/4/17 20:14:47.519347 -04:002018/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;--因?yàn)镾YSTIMESTAMP本身帶有時(shí)區(qū)，所以報(bào)錯(cuò)ORA-00932: 數(shù)據(jù)類型不一致: 應(yīng)為 TIMESTAMP, 但卻獲得 TIMESTAMP WITH TIME ZONE。

向AI問一下細(xì)節(jié)

TIMESTAMP和TIMESTAMP WITH TIME ZONE之間的總結(jié)

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽