[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,我的猜测也许对的就是时区。