浅析Oracle对date日期格式的存储

这两天有一套库报了ORA-01841错误,于是好好的研究了一下Oracle对Date类型日期格式的存储方式。那么对于date类型的日期,Oracle在底层会以7个byte来进行存储。分别是世纪、年、月、日、小时、分钟和秒。Oracle内部存储date结构如下:

BYTE  Meaning
----  -------
1   Century    -- stored in excess-100 notation
2   Year       -- stored in excess-100 notation
3   Month      -- stored in 0 base notation
4   Day        -- stored in 0 base notation
5   Hour       -- stored in excess-1 notation
6   Minute     -- stored in excess-1 notation
7   Second     -- stored in excess-1 notation

说这些比较抽象,我们先来看个例子。

SQL> select to_char(HIREDATE,'YYYY-MM-DD HH24:MI:SS') from emp where rownum<=1;

TO_CHAR(HIREDATE,'Y
-------------------
1980-12-17 00:00:00

SQL> select rowid from emp where rownum<=1;

ROWID
------------------
AAASZHAAEAAAACXAAA

SQL> select dbms_rowid.rowid_block_number('AAASZHAAEAAAACXAAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAASZHAAEAAAACXAAA')
---------------------------------------------------
151

这里我们查出来该日期数据存在于文件4的151号块当中,我们使用bbed来查看数据库内部是怎么存储日期数据的。

BBED> set dba 4,151
DBA             0x01000097 (16777367 4,151)

BBED> p kdbr
sb2 kdbr[0]                                 @118      8050
sb2 kdbr[1]                                 @120      8007
sb2 kdbr[2]                                 @122      7964
sb2 kdbr[3]                                 @124      7923
sb2 kdbr[4]                                 @126      7878
sb2 kdbr[5]                                 @128      7837
sb2 kdbr[6]                                 @130      7796
sb2 kdbr[7]                                 @132      7756
sb2 kdbr[8]                                 @134      7718
sb2 kdbr[9]                                 @136      7675
sb2 kdbr[10]                                @138      7637
sb2 kdbr[11]                                @140      7599
sb2 kdbr[12]                                @142      7560
sb2 kdbr[13]                                @144      7521

BBED> p *kdbr[0]
rowdata[529]
------------
ub1 rowdata[529]                            @8150     0x2c

BBED> x /rnccntnnn
rowdata[529]                                @8150
------------
flag@8150: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8151: 0x01
cols@8152:    8

col    0[3] @8153: 7369
col    1[5] @8157: SMITH
col    2[5] @8163: CLERK
col    3[3] @8169: 7902
col    4[7] @8173: 17-DEC-80
col    5[2] @8181: 800
col    6[0] @8184: *NULL*
col    7[2] @8185: 20

BBED> x /r
rowdata[529]                                @8150
------------
flag@8150: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8151: 0x01
cols@8152:    8

col    0[3] @8153:  0xc2  0x4a  0x46
col    1[5] @8157:  0x53  0x4d  0x49  0x54  0x48
col    2[5] @8163:  0x43  0x4c  0x45  0x52  0x4b
col    3[3] @8169:  0xc2  0x50  0x03
col    4[7] @8173:  0x77  0xb4  0x0c  0x11  0x01  0x01  0x01
col    5[2] @8181:  0xc2  0x09
col    6[0] @8184: *NULL*
col    7[2] @8185:  0xc1  0x15

通过bbed,我们发现我们的日期在内部存储为77 b4 0c 11 01 01 01,通过转换成10进制,得到的数据是119 180 12 17 01 01 01。这7个字节正好是代表了世纪、年、月、日、小时、分钟和秒。
【世纪】
这个值分为公园前(BC)和公元后(AD),范围在-4712年到9999年之间取,并且不能等于0。当前我们是公园后1980年,那么也就是取前面两位19,在执行加100。所以结果是119。如果是公园前-350年,那就是100+(-3)=97。必须要指出一点的是,没有公园0年。要么是-1,也就是公园前-1年,要么是1,公元后1年。如果输入0,就会报ORA-01841:(full) year must be between -4713 and +9999, and not be 0。

SQL> create table t3(t date);

Table created.

SQL> insert into t3 values(to_date('-350-12-07  23:59:59', 'syyyy-mm-dd hh24:mi:ss'));

1 row created.

SQL> commit ;

Commit complete.

SQL> select dump(t) from t3;

DUMP(T)
--------------------------------------------------------------------------------
Typ=12 Len=7: 97,50,12,7,24,60,60

SQL> insert into t3 values(to_date('-0-12-07 23:59:59', 'syyyy-mm-dd hh24:mi:ss'));
insert into t3 values(to_date('-0-12-07 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

【年】
这个值和世纪一样,是我们的1980后面的两位(80)取出来加上100,这里显示是180。如果是公园前-350年,就是取出(-50)再加上100,则为50。
【月和日】
这个值就和现实的值一样。月的取值是1-12,日的取值是1-31。
【小时、分钟和秒】
这个值是我们所取的值+1,比如小时就是1-24,分钟就是1-61,秒也是1-61。
好,知道计算公式了,我们来反推一个。比如emp的第2行日期值。

BBED> p *kdbr[1]
rowdata[486]
------------
ub1 rowdata[486]                            @8107     0x2c

BBED> x /r
rowdata[486]                                @8107
------------
flag@8107: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8108: 0x01
cols@8109:    8

col    0[3] @8110:  0xc2  0x4b  0x64
col    1[5] @8114:  0x41  0x4c  0x4c  0x45  0x4e
col    2[8] @8120:  0x53  0x41  0x4c  0x45  0x53  0x4d  0x41  0x4e
col    3[3] @8129:  0xc2  0x4d  0x63
col    4[7] @8133:  0x77  0xb5  0x02  0x14  0x01  0x01  0x01 
col    5[2] @8141:  0xc2  0x11
col    6[2] @8144:  0xc2  0x04
col    7[2] @8147:  0xc1  0x1f

可以看到这里的值是77 B5 02 14 01 01 01,转换成10进制就是119 181 02 20 01 01 01。世纪和年的公式,反推-100。119-100=19,181-100=81。结果为1981。月和日的公式,保持一致02-20时间、分钟和秒的公式,反推-1,时间为00:00:00。最终得出的结论:1981-02-20 00:00:00。我们去数据库中查一下。

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select HIREDATE from emp where rownum<=2;

HIREDATE
-------------------
1980-12-17 00:00:00
1981-02-20 00:00:00

最后有一点需要注意的地方,正常情况我们直接对数据进行dump,得出的结果和我们用bbed查看的结果是一致的,但是如果用了to_date函数,在进行dump就不一致了。需要进行新的换算。我们先来看一下区别。

SQL> select HIREDATE from emp where rownum<=2;

HIREDATE
-------------------
1980-12-17 00:00:00
1981-02-20 00:00:00

SQL> select dump(HIREDATE) from emp where rownum<=2;

DUMP(HIREDATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 119,180,12,17,1,1,1
Typ=12 Len=7: 119,181,2,20,1,1,1

SQL> select dump(tO_date('1981-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS')) from dual;

DUMP(TO_DATE('1981-02-2000:00:00
--------------------------------
Typ=13 Len=8: 189,7,2,20,0,0,0,0

这里,我们发现我们使用to_date函数后,结果和直接dump效果不一致,前面的Typ从12变成了13,Len从7变成了8。用了8位byte来表现日期值。那么这又怎么算呢?公式如下所示:

Byte 1 - Base 256 year modifier
2 - Base 256 year
3 - Month
4 - Day
5 - Hours
6 - Minutes
7 - Seconds
8 – Unused

计算公式=Byte 1+Byte 2*256,那么就是189+7*256=1981.其他值和现实中的值一样。最后第8 Byte是unused的。
出现这种情况主要是因为Type 13不是Oracle发布的3GL interfaces。它只是在PL/SQL中为了方便日期计算而设计的。而且这种结构和C编译器有关系。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *