Oracle基本資料型別儲存格式淺析（三）——日期型別（二）

這篇文章描述TIMESTAMP型別的資料在Oracle中是以何種格式存放的。


下面透過一個例子進行說明。







SQL> create table test_time (col_time timestamp);


表已建立。


SQL> insert into test_time values (to_timestamp('0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));


已建立 1 行。


SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));


已建立 1 行。


SQL> insert into test_time values (to_timestamp('9999-12-31 23:59:59.999999', 'syyyy-mm-dd hh24:mi:ss.ff'));


已建立 1 行。


SQL> insert into test_time values (to_timestamp('-0001-1-1 0:0:0.0', 'syyyy-mm-dd hh24:mi:ss.ff'));


已建立 1 行。


SQL> insert into test_time values (to_timestamp('-0100-3-4 13:2:3.234015', 'syyyy-mm-dd hh24:mi:ss.ff'));


已建立 1 行。


SQL> insert into test_time values (systimestamp);


已建立 1 行。



SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));


已建立 1 行。


SQL> commit;


提交完成。


SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
  2  from test_time;


TIME                           DUMP_TIME
------------------------------ ----------------------------------------------------
0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232


已選擇7行。


與DATE型別對比可以發現，對於TIMESTAMP型別，如果不包含微秒資訊或者微秒值為0，那麼儲存結果和DATE完全相同。當微秒值為0時，Oracle為了節省空間，不會儲存微秒資訊。


如果毫秒值不為0，Oracle把微秒值當作一個9位數的數字來儲存。


比如999999000，儲存為59,154,198,24。234015000儲存為13,242,201,24。


SQL> select to_char(999999000, 'xxxxxxxxxx') from dual;


TO_CHAR(999
-----------
   3b9ac618


SQL> select to_number('3b', 'xxx') one, to_number('9a', 'xxx') two,
  2  to_number('c6', 'xxx') three, to_number('18', 'xxx') four from dual;


       ONE        TWO      THREE       FOUR
---------- ---------- ---------- ----------
        59        154        198         24


SQL> select to_char(234015000, 'xxxxxxxx') from dual;


TO_CHAR(2
---------
  df2c918


SQL> select to_number('d', 'xxx') one, to_number('f2', 'xxx') two,
  2  to_number('c9', 'xxx') three, to_number('18', 'xxx') four from dual;


       ONE        TWO      THREE       FOUR
---------- ---------- ---------- ----------
        13        242        201         24





另外，注意一點，不指定精度的情況下，TIMESTAMP預設取6位。長度超過6位，會四捨五入到6位。如果希望儲存9位的TIMESTAMP，必須明確指定精度。


SQL> alter table test_time modify (col_time timestamp(9));


表已更改。


SQL> insert into test_time values (to_timestamp('2000-1-1 0:0:0.123456789', 'syyyy-mm-dd hh24:mi:ss.ff9'));


已建立 1 行。


SQL> select to_char(col_time, 'syyyy-mm-dd hh24:mi:ss.ff9') time, dump(col_time) dump_time
  2  from test_time;


TIME                           DUMP_TIME
------------------------------ ---------------------------------------------------
0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,101,1,1,1,1,1
2000-01-01 00:00:00.000000000 Typ=180 Len=7: 120,100,1,1,1,1,1
9999-12-31 23:59:59.999999000 Typ=180 Len=11: 199,199,12,31,24,60,60,59,154,198,24
-0001-01-01 00:00:00.000000000 Typ=180 Len=7: 100,99,1,1,1,1,1
-0100-03-04 13:02:03.234015000 Typ=180 Len=11: 99,100,3,4,14,3,4,13,242,201,24
2004-12-15 16:14:52.738000000 Typ=180 Len=11: 120,104,12,15,17,15,53,43,252,252,128
2000-01-01 00:00:00.123457000 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,232
2000-01-01 00:00:00.123456789 Typ=180 Len=11: 120,100,1,1,1,1,1,7,91,205,21


已選擇8行。