[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)