[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,我的猜测也许对的就是时区。
作者:lfree原文地址:https://www.cnblogs.com/lfree/p/18455082

%s 个评论

要回复文章请先登录注册