[20241009]oracle timestamp with time zone数据类型的存储.txt

来源：这里教程网时间：2026-03-03 20:41:11 作者：

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