Oracle基本資料型別儲存格式淺析（三）——日期型別（一）

這篇文章描述DATE型別的資料在Oracle中是以何種格式存放的。


下面透過一個例子進行說明。


SQL> create table test_date (date_col date);


表已建立。


SQL> insert into test_date values (to_date('2000-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'));


已建立 1 行。


SQL> insert into test_date values (to_date('1-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'));


已建立 1 行。


SQL> insert into test_date values (to_date('-1-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));


已建立 1 行。


SQL> insert into test_date values (to_date('-101-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));


已建立 1 行。


SQL> insert into test_date values (to_date('-4712-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));


已建立 1 行。


SQL> insert into test_date values (to_date('9999-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'));


已建立 1 行。


SQL> insert into test_date values (sysdate);


已建立 1 行。


SQL> insert into test_date values (to_date('-4713-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'));
insert into test_date values (to_date('-4713-1-1 0:0:0', 'syyyy-mm-dd hh24:mi:ss'))
                                      *
ERROR 位於第 1 行:
ORA-01841: （全）年度值必須介於 -4713 和 +9999 之間，且不為 0



SQL> insert into test_date values (to_date('0000-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'));
insert into test_date values (to_date('0000-1-1 0:0:0', 'yyyy-mm-dd hh24:mi:ss'))
                                      *
ERROR 位於第 1 行:
ORA-01841: （全）年度值必須介於 -4713 和 +9999 之間，且不為 0



SQL> col dump_date format a80
SQL> select to_char(date_col, 'syyyy-mm-dd hh24:mi:ss'), dump(date_col) dump_date from test_date;


TO_CHAR(DATE_COL,'SY DUMP_DATE
-------------------- ---------------------------------------
2000-01-01 00:00:00 Typ=12 Len=7: 120,100,1,1,1,1,1
0001-01-01 00:00:00 Typ=12 Len=7: 100,101,1,1,1,1,1
-0001-01-01 00:00:00 Typ=12 Len=7: 100,99,1,1,1,1,1
-0101-01-01 00:00:00 Typ=12 Len=7: 99,99,1,1,1,1,1
-4712-01-01 00:00:00 Typ=12 Len=7: 53,88,1,1,1,1,1
9999-12-31 23:59:59 Typ=12 Len=7: 199,199,12,31,24,60,60
2004-12-15 13:56:19 Typ=12 Len=7: 120,104,12,15,14,57,20


已選擇7行。





透過最後兩條語句已經可以看出Oracle的DATE型別的取值範圍是公元前4712年1月1日至公元9999年12月31日。而且根據日期的特定，要不然是公元1年，要不然是公元前1年，不會出現0年的情況。


日期型別長度是7，7個位元組分別表示世紀、年、月、日、時、分和秒。


由於不會出現0的情況，月和日都是按照原值儲存的，月的範圍是1～12，日的範圍是1～31。


由於時、分、秒都會出現0的情況，因此儲存時採用原值加1的方式。0時儲存為1，13時儲存為14，23時儲存為24。分和秒的情況與小時類似。小時的範圍是0～23，在資料庫中以1～24儲存。分和秒的範圍都是0～59，在資料庫中以1～60儲存。


年和世紀的情況相對比較複雜，可分為公元前和公元后兩種情況。由於最小的世紀的值是-47（公元前4712年），最大值是99（公元9999年）。為了避免負數的產生，oracle把世紀加100儲存在資料庫中。公元2000年，世紀儲存為120，公元9999年，世紀儲存為199，公元前101年，世紀儲存為99（100+(-1)），公元前4712年，世紀儲存為53（100+(-47)）。


注意，對於公元前1年，雖然已經是公元前了，但是表示世紀的前兩位的值仍然是0，因此，這時的儲存的世紀的值仍然是100。世紀的範圍是-47～99，儲存的值是53～199。


年的儲存與世紀的儲存方式類似，也把年的值加上100進行儲存。對於公元2000年，年保持為100，公元1年儲存為101，公元2004年儲存為104，公元9999年儲存為199，公元前1年，儲存為99（100+(-1)），公元前101年，儲存為99（100+(-1)），公元前4712年儲存為88（100+(-12)）。對於公元前的年，儲存的值總是小於等於100，對於公元后的年，儲存的值總是大於等於100。年的範圍是0～99，儲存的值是1～199。


注意：一般的世紀，都包含了100年，而對於0世紀，由於包含公元前和公元后兩部分且不包含0年，因此包含了198年。