[20200121]CURRENT_TIMESTAMP LOCALTIMESTAMP SYSTIMESTAMP的不同.TXT

[20200121]CURRENT_TIMESTAMP LOCALTIMESTAMP SYSTIMESTAMP的不同.TXT

--//別人的問題，實際上我自己也不知道：
--//我的環境定義如下：
$ set | grep NLS
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9'
NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM'
NLS_TIME_TZ_FORMAT='HH24.MI.SSXFF TZH:TZM'

1.環境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:
SCOTT@book> select current_timestamp ,systimestamp from dual ;
CURRENT_TIMESTAMP                    SYSTIMESTAMP
------------------------------------ ------------------------------------
2020-01-21 09:01:43.030480000 +08:00 2020-01-21 09:01:43.030475000 +08:00

SCOTT@book> select current_timestamp ,systimestamp,current_timestamp from dual ;
CURRENT_TIMESTAMP                    SYSTIMESTAMP                         CURRENT_TIMESTAMP
------------------------------------ ------------------------------------ ------------------------------------
2020-01-21 09:01:50.968273000 +08:00 2020-01-21 09:01:50.968265000 +08:00 2020-01-21 09:01:50.968273000 +08:00

--//不知道大家注意沒有，最後一個欄位先執行，看秒後面數值。(030475000<030480000)
--//而第2次執行，先執行第3個欄位的current_timestamp，但是第1個current_timestamp數值從第3個輸出值一致。
--//兩個都帶有時區，很明顯格式使用NLS_TIMESTAMP_TZ_FORMAT的定義。

--//連結：

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.

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH
TIME ZONE. The time zone offset reflects the current local time of the SQL session. If you omit precision, then the
default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH
TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.

SCOTT@book> select current_timestamp ,systimestamp,localtimestamp  from dual ;
CURRENT_TIMESTAMP                     SYSTIMESTAMP                         LOCALTIMESTAMP
------------------------------------- ------------------------------------ -----------------------------
2020-01-21 09:07:52.129754000 +08:00  2020-01-21 09:07:52.129747000 +08:00 2020-01-21 09:07:52.129754000

SCOTT@book> select localtimestamp,systimestamp,current_timestamp  from dual ;
LOCALTIMESTAMP                SYSTIMESTAMP                         CURRENT_TIMESTAMP
----------------------------- ------------------------------------ ------------------------------------
2020-01-21 09:12:36.765938000 2020-01-21 09:12:36.765932000 +08:00 2020-01-21 09:12:36.765938000 +08:00

--//LOCALTIMESTAMP的格式受NLS_TIMESTAMP_FORMAT控制。
--//LOCALTIMESTAMP 的輸出與CURRENT_TIMESTAMP的輸出一樣(不包括時區部分,當前客戶時區與伺服器一致)，也就是運算僅僅1次。

--//設定時區不一致看看。
SCOTT@book> alter session set time_zone='+0:00';
Session altered.

SCOTT@book> select localtimestamp,systimestamp,current_timestamp  from dual ;
LOCALTIMESTAMP                SYSTIMESTAMP                         CURRENT_TIMESTAMP
----------------------------- ------------------------------------ ------------------------------------
2020-01-21 01:17:05.667409000 2020-01-21 09:17:05.667403000 +08:00 2020-01-21 01:17:05.667409000 +00:00

--//注意看秒後面的數值,CURRENT_TIMESTAMP與LOCALTIMESTAMP存在轉換。一些細節自己慢慢體會。
--//注意一定注意要設定帶時區與不帶時區的格式，這樣看資料庫一些時間資訊才比較準確。
--//我自己在上面犯過一些錯誤。像連結http://blog.itpub.net/267265/viewspace-2152520/，如果不帶時區顯示，很容易出現歧義性。
NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9'
NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM'

--//可以不要後面9。FF9改成FF，這樣秒後面僅僅6位小數點，自己就是這樣設定的，為了測試設定FF9。