Oracle中的TIMESTAMP型別

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行。