date类型的内部结构

开发库>create table t_test (d date,n number, v varchar2(20));

Table created.

开发库>insert into t_test values(sysdate,100,'中国');

1 row created.

开发库>commit;

Commit complete.

开发库>
开发库>select * from t_test;

D                     N V
------------ ---------- --------------------
06-JAN-13           100 中国

开发库>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

开发库>select * from t_test;

D                            N V
------------------- ---------- --------------------
2013-01-06 17:00:50        100 中国

开发库>select dump(d,16),dump(n,16),dump(v,16) from t_test;

DUMP(D,16)
--------------------------------------------------------------------------------
DUMP(N,16)
--------------------------------------------------------------------------------
DUMP(V,16)
--------------------------------------------------------------------------------
Typ=12 Len=7: 78,71,1,6,12,1,33
Typ=2 Len=2: c2,2
Typ=1 Len=4: d6,d0,b9,fa

开发库>select dump(to_date('2013-01-06 17:00:50', 'yyyy-mm-dd hh24:mi:ss'),16)  from dual;

DUMP(TO_DATE('2013-01-0617:00:50
--------------------------------
Typ=13 Len=8: 7,dd,1,6,11,0,32,0    -- 注意与上面不一致

开发库>select dump(100, 16) from dual;

DUMP(100,16)
-----------------
Typ=2 Len=2: c2,2　　　　　　　　　　

开发库>select dump('中国', 16) from dual;

DUMP('中国',16)
-------------------------
Typ=96 Len=4: d6,d0,b9,fa

开发库>declare
  2    d date;
  3  begin
  4    dbms_stats.convert_raw_value('78710106120133', d);
  5    dbms_output.put_line(d);
  6  end;
  7  /

PL/SQL procedure successfully completed.

开发库>set serveroutput on size 20000;
开发库>/
2013-01-06 17:00:50

PL/SQL procedure successfully completed.

注：结果ok

开发库>
开发库>declare
  2    d date;
  3  begin
  4    dbms_stats.convert_raw_value('7dd010611003200', d);
  5    dbms_output.put_line(d);
  6  end;
  7  /
9179-01-06 16:01:49

PL/SQL procedure successfully completed.

结果：不对

 

以上说明date数据类型在内存和块中的根式是不一样的．

 

在google上找到一篇文章，上面的说法得到进一步的说明：

原文如下： http://www.ixora.com.au/notes/date_representation.htm

 

Oracle Internals Notes

Internal representation of the DATE datatype

As with other datatypes, stored DATEs are always preceded by a length byte. The length byte is 0xFF for NULLs, or 7 bytes for known DATEs. The internal representation of DATEs is quite simple and can be easily seen using the dump function as follows.

SQL> create table dates (d date); Table created. SQL> insert into dates values (to_date('18/APR/2002 15:06:00', 'DD/MON/YYYY HH24:MI:SS')); 1 row created. SQL> select dump(d) from dates; DUMP(D) -------------------------------------------------------------------------------- Typ=12 Len=7: 120,102,4,18,16,7,1

The first two bytes represent the century and year respectively. Each of these bytes have an offset of 100 to allow for the negative centuries and years required for BC dates. For example, the byte pair 96,8 would represent the year 492 BC (the year of the battle of Marathon). The 3rd and 4th bytes represent the month and the day of that month respectively. The last three bytes represent the hour, minute, and second. Each of these time bytes have an offset of 1 to ensure that dates can never contain null bytes. So the contents of the seven bytes are as follows.

byte 1: century + 100 byte 2: year + 100 byte 3: month byte 4: day of month byte 5: hour + 1 byte 6: minute + 1 byte 7: second + 1

This only applies to stored dates. Oracle actually uses a slightly different representation internally when working with dates in memory.

SQL> select dump(to_date('18/APR/2002 15:06:00', 'DD/MON/YYYY HH24:MI:SS')) from dual; DUMP(TO_DATE('18/APR/200215:06:00 --------------------------------- Typ=13 Len=8: 210,7,4,18,15,6,0,0

Here the datatype number is 13, instead of 12. The memory structure has been padded to a 4-byte boundary. The time bytes do not have any offset. And the century and year are represented as a single signed two byte number. Because this dump was taken on a machine with a little-endian architecture, the bytes are reversed and should be read as 7,210 or 0x7D2, which is decimal 2002. The corresponding bytes for 492 BC would be 20,254. That reverses to 254,20 or 0xFE14, which is -492 in two's complement notation. These details are of course platform. specific.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-752205/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-752205/