Oracle基本数据类型存储格式浅析（三）——日期类型（二）

来源：yangtingkun.itpub.net

---

这篇文章描述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行。