[20191219]oracle timestamp数据类型的存储.txt

[20191219]oracle timestamp数据类型的存储.txt --//在优化一个项目,里面的日期类型基本选择都是timestamp类型.自己在以前工作中很少遇到这种类型. --//倒是遇到几例定义date类型,而绑定变量是timestamp的情况,导致出现隐式转换. --//顺便说一下,我个人一直任何使用date类型足以,不知道在什么情况下需要这么高精度的时间类型. --//我仅仅想到一种可能,假设1秒内存在上百上千的业务,里面的日期精度这个时候也许很重要. 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> create table t(id number,cdate timestamp); Table created. SCOTT@book> insert into t values (1,sysdate); 1 row created. SCOTT@book> insert into t values (2,systimestamp); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select id,dump(cdate,16) c50,dump(cdate,10) c60 ,cdate from t; ID C50                                                C60                                                   CDATE -- -------------------------------------------------- ----------------------------------------------------- ---------------------------  1 Typ=180 Len=7: 78,77,c,13,10,2b,8                  Typ=180 Len=7: 120,119,12,19,16,43,8                  2019-12-19 15:42:07.000000  2 Typ=180 Len=11: 78,77,c,13,10,2b,f,1e,e6,db,b0     Typ=180 Len=11: 120,119,12,19,16,43,15,30,230,219,176 2019-12-19 15:42:14.518446 --//可以发现timestamp类型如果没有秒后面的精度,仅仅占用7个字节.对于格式很容易猜测出来. --//前1,2位各减去100,对应就是2019年. 月日不可能出现0的情况,也就是相互对应.而时分秒有0的出现,在原来的时间基础上+1. --//比如时分秒=15:42:07,对应的编码(10进制)就是16 43 8. --//剩下的难点就是秒后面的数值.比如1e,e6,db,b0如何表示.518446. SCOTT@book> @ 16to10 1ee6dbb0 16 to 10 DEC ------------    518446000 --//可以猜测相当于0.518446*power(10,9)转换为16进制就是对应编码. --//0x10000000 = 268435456,插入秒后这样的时间看看? SCOTT@book> insert into t values (3,to_timestamp('2019-12-20 0:0:0.268435456', 'syyyy-mm-dd hh24:mi:ss.ff9')); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select id,dump(cdate,16) c50,dump(cdate,10) c60 ,cdate from t;  ID C50                                            C60                                                   CDATE --- ---------------------------------------------- ----------------------------------------------------- --------------------------   1 Typ=180 Len=7: 78,77,c,13,10,2b,8              Typ=180 Len=7: 120,119,12,19,16,43,8                  2019-12-19 15:42:07.000000   2 Typ=180 Len=11: 78,77,c,13,10,2b,f,1e,e6,db,b0 Typ=180 Len=11: 120,119,12,19,16,43,15,30,230,219,176 2019-12-19 15:42:14.518446   3 Typ=180 Len=11: 78,77,c,14,1,1,1,f,ff,fe,38    Typ=180 Len=11: 120,119,12,20,1,1,1,15,255,254,56     2019-12-20 00:00:00.268435 --//噢明白了缺省timestamp类型是保留6位,除非明确精度9. SCOTT@book> @ desc t            Name   Null?    Type            ------ -------- -------------     1      ID              NUMBER     2      CDATE           TIMESTAMP(6) SCOTT@book> alter table t add ( ccdate timestamp(9)); Table altered. SCOTT@book> insert into t (id ,ccdate) values (4,to_timestamp('2019-12-20 0:0:0.268435456', 'syyyy-mm-dd hh24:mi:ss.ff9')); 1 row created. SCOTT@book> insert into t (id ,ccdate) values (5,to_timestamp('2019-12-20 0:0:0.999999999', 'syyyy-mm-dd hh24:mi:ss.ff9')); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select id,dump(ccdate,16) c50,dump(ccdate,10) c60 ,ccdate from t where id>=4;  ID C50                                          C60                                                CCDATE --- -------------------------------------------- -------------------------------------------------- -----------------------------   4 Typ=180 Len=11: 78,77,c,14,1,1,1,10,0,0,0    Typ=180 Len=11: 120,119,12,20,1,1,1,16,0,0,0       2019-12-20 00:00:00.268435456   5 Typ=180 Len=11: 78,77,c,14,1,1,1,3b,9a,c9,ff Typ=180 Len=11: 120,119,12,20,1,1,1,59,154,201,255 2019-12-20 00:00:00.999999999 --//999999999 = 0x3b9ac9ff. --//主要看看后面的0是否没有,可以发现还是存在的,也就是要么占用7字节,要么占用11字节. --//补充一点实际上精度自己可以控制,最大是9. SCOTT@book> alter table t add ( cccdate timestamp(8)); Table altered. SCOTT@book> alter table t add ( xdate timestamp(10)); alter table t add ( xdate timestamp(10))                                     * ERROR at line 1: ORA-30088: datetime/interval precision is out of range SCOTT@book> @ desc t            Name      Null?    Type            --------- -------- -------------     1      ID                 NUMBER     2      CDATE              TIMESTAMP(6)     3      CCDATE             TIMESTAMP(9)     4      CCCDATE            TIMESTAMP(8)