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