oracle的dtime,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行。