[20241009]oracle timestamp with time zone資料型別的儲存.txt

[20241009]oracle timestamp with time zone資料型別的儲存.txt

--//放假前遇到的問題，開發在表中定義了幾個timestamp with time zone的資料型別，及時更正對方的錯誤，完全沒有使用這樣的資料
--//型別。類似的問題我以前就遇到，比如全部應用程式的表凡是varchar2資料型別都被定義為nvarchar2資料型別，date資料型別都被
--//定義為timestamp資料型別.

--//第1種情況多數情況下開始是混合使用，一些開發定義varchar2型別，一些定義為nvarchar2型別，到後期才發現問題，因為這樣等值
--//連線時會存在全表掃描，沒有辦法上線全部統一到nvarchar2資料型別。

--//第2種情況，一些應用程式傳入的日期引數是timestamp型別，導致使用欄位使用date資料型別時存在隱式轉換，沒有辦法將表欄位有
--//關日期型別的欄位全部換成timestamp資料型別，我遇到的專案多數是java以及pb開發的專案。

--//順便說一下,我個人一直任認為使用date型別足以,不知道在什麼情況下需要這麼高精度的時間型別.

--//從許多方面講，國內的許多專案都是豆腐渣工程，一開始就沒有很好的設計和規劃，許多程式設計師不瞭解資料型別的儲存結構，各自為
--//戰,把資料庫效能問題放在最後，甚至上線不到一年還沒驗收都執行緩慢，更多是靠良好的硬體掩蓋拙劣的設計。

--//自己也不瞭解timestamp with time zone資料型別的儲存，以前寫過一篇[20191219]oracle timestamp資料型別的儲存.txt，看看帶
--//time zone的情況.

1.環境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

2.測試：
SCOTT@book01p> create table t(id number,c1 timestamp(10) with time zone ,c2 timestamp(10));
create table t(id number,c1 timestamp(10) with time zone ,c2 timestamp(10))
*
ERROR at line 1:
ORA-30088: datetime/interval precision is out of range
--//timestamp 最高精度是9，輸入10報錯。

SCOTT@book01p> create table t(id number,c1 timestamp(9) with time zone ,c2 timestamp(9));
Table created.

insert into t values (1,sysdate,sysdate);
insert into t values (2,systimestamp,systimestamp);
commit ;


SCOTT@book01p> select id,dump(c1,16) c60,dump(c1,10) c60 ,dump(c2,16) c60 ,dump(c2,10) c60 ,c1 c40,c2 c40 from t where id=1
2 @ pr
==============================
ID : 1
C60 : Typ=181 Len=13: 78,7c,a,9,1,30,3a,0,0,0,0,1c,3c
C60 : Typ=181 Len=13: 120,124,10,9,1,48,58,0,0,0,0,28,60
C60 : Typ=180 Len=7: 78,7c,a,9,9,30,3a
C60 : Typ=180 Len=7: 120,124,10,9,9,48,58
C40 : 2024-10-09 08:47:57.000000000 +08:00
C40 : 2024-10-09 08:47:57.000000000
PL/SQL procedure successfully completed.
--//對比可以發現timestamp帶時區的型別181，不帶時區的型別是180.
--//我插入sysdate，也就是沒有秒後面的單位，可以發現這樣情況下timestamp佔用7位元組，沒有後面的秒,節省了4個位元組對比下面的記
--//錄,對於格式很容易猜測出來.
--//前1,2位各減去100,對應就是2024年，月日不可能出現0的情況,也就是相互對應.而時分秒有0的出現,在原來的時間基礎上+1.
--//比如時分秒=08：47：57,對應的編碼(10進位制)就是9 48 58.

--//再來看看帶時區的時部分是1，也就是儲存的UTC時間，使用9減去8=1，後面跟著0,0,0,0,1c,3c。前4位表示秒後面的數值，這裡是0。
--//最後2位 1c，3c我估計與時區有關。

SCOTT@book01p> select id,dump(c1,16) c60,dump(c1,10) c60 ,dump(c2,16) c60 ,dump(c2,10) c60 ,c1 c40,c2 c40 from t where id=2
2 @pr
==============================
ID : 2
C60 : Typ=181 Len=13: 78,7c,a,9,1,30,3a,24,d0,d9,b8,1c,3c
C60 : Typ=181 Len=13: 120,124,10,9,1,48,58,36,208,217,184,28,60
C60 : Typ=180 Len=11: 78,7c,a,9,9,30,3a,24,d0,d9,b8
C60 : Typ=180 Len=11: 120,124,10,9,9,48,58,36,208,217,184
C40 : 2024-10-09 08:47:57.617667000 +08:00
C40 : 2024-10-09 08:47:57.617667000
PL/SQL procedure successfully completed.

--//同上，這裡timestamp佔用11位元組，帶時區的佔用13位元組，可以發現帶時區都是佔用13位元組，因為最後儲存的是1c，3c。
--//617667000 = 0x24d0d9b8,與24,d0,d9,b8正好對上。

--//基本可以看出timestamp with time zone的的儲存：
--//年 佔 2位元組 ，各自減去100，合在一起表示年。
--//月 佔 1位元組
--//日 佔 1位元組
--//時分秒 因為有0存在，在原來基礎上+1. 而時還要減少8(對應8時區)，似乎儲存的是utc時間。
--//接著4位是秒後面的數值。
--//最後2位 1c,3c 我估計與時區有關。

SCOTT@book01p> insert into t values (3,'2024-10-09 10:47:57.120000000 +09:00','2024-10-09 08:47:57.120000000');
1 row created.

SCOTT@book01p> commit ;
Commit complete.

--//注意我帶入字串可以，是因為我定義環境變數
$ env | grep -i nls
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT=HH24.MI.SSXFF TZH:TZM
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

SCOTT@book01p> select id,dump(c1,16) c60,dump(c1,10) c60 ,dump(c2,16) c60 ,dump(c2,10) c60 ,c1 c40,c2 c40 from t where id=3
2 @ pr
==============================
ID : 3
C60 : Typ=181 Len=13: 78,7c,a,9,2,30,3a,7,27,e,0,1d,3c
C60 : Typ=181 Len=13: 120,124,10,9,2,48,58,7,39,14,0,29,60
C60 : Typ=180 Len=11: 78,7c,a,9,9,30,3a,7,27,e,0
C60 : Typ=180 Len=11: 120,124,10,9,9,48,58,7,39,14,0
C40 : 2024-10-09 10:47:57.120000000 +09:00
C40 : 2024-10-09 08:47:57.120000000
PL/SQL procedure successfully completed.

--//120000000 = 0x07270e00.
--//帶時區的最後2位是1d 3c，前者(1c,3c).而時部分是2，10-9+1=2.

--//另外注意直接在SQL中使用的timestamp型別與儲存在資料庫的不同。

--//先複習date資料型別：
SCOTT@book01p> select dump(sysdate,16),dump(sysdate,10),sysdate from dual
2 @pr
==============================
DUMP(SYSDATE,16) : Typ=13 Len=8: e8,7,a,9,9,1d,21,0
DUMP(SYSDATE,10) : Typ=13 Len=8: 232,7,10,9,9,29,33,0
SYSDATE : 2024-10-09 09:29:33
PL/SQL procedure successfully completed.

--//型別13。年是e807 ,顛倒過來 07e8 = 2024.
--//月日時分秒保持原樣，最後加了一個0x0.佔用8位元組。

--//先timestamp資料型別：

SCOTT@book01p> select dump(systimestamp,16),dump(systimestamp,10),systimestamp from dual
2 @pr
==============================
DUMP(SYSTIMESTAMP,16) : Typ=188 Len=20: e8,7,a,9,1,20,3b,0,e0,fc,fb,8,8,0,5,0,0,0,0,0
DUMP(SYSTIMESTAMP,10) : Typ=188 Len=20: 232,7,10,9,1,32,59,0,224,252,251,8,8,0,5,0,0,0,0,0
SYSTIMESTAMP : 2024-10-09 09:32:59.150732 +08:00
PL/SQL procedure successfully completed.

--//型別188.年是e807 ,顛倒過來 07e8 = 2024.
--//月日分秒保持原樣，最後加了一個0x0.佔用8位元組。時9-8=1.最後加了一個0x0.表示秒之前的值。
--//秒後面的數值我看了很久才明白：
--//150732000 = 0x08fbfce0 顛倒過來就是 e0fcfb08,
--//剩下的8,0,5,0,0,0,0,0 表示什麼我就猜測不到了，估計第1個8與時區相關。
--//佔用20位元組。

SCOTT@book01p> select dump(to_timestamp_tz('2024-10-09 10:47:57.120000000 +09:00','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),16) c60 from dual ;
C60
------------------------------------------------------------
Typ=188 Len=20: e8,7,a,9,1,2f,39,0,0,e,27,7,9,0,5,0,0,0,0,0

SCOTT@book01p> select dump(to_timestamp_tz('2024-10-09 10:47:57.120000000 +09:00','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'),10) c70 from dual ;
C70
----------------------------------------------------------------------
Typ=188 Len=20: 232,7,10,9,1,47,57,0,0,14,39,7,9,0,5,0,0,0,0,0

--//120000000 = 0x07270e00 顛倒過來就是 000e2707
--//剩下的9,0,5,0,0,0,0,0 表示什麼我就猜測不到了，估計第1個9,我的猜測也許對的就是時區。