date型別的內部結構
開發庫>select * from t_test;
------------ ---------- --------------------
06-JAN-13 100 中國
------------------- ---------- --------------------
2013-01-06 17:00:50 100 中國
--------------------------------------------------------------------------------
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
--------------------------------
Typ=13 Len=8: 7,dd,1,6,11,0,32,0 --注意與上面不一致
-----------------
Typ=2 Len=2: c2,2
-------------------------
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 /
開發庫>/
2013-01-06 17:00:50
開發庫>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
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.
This only applies to stored dates. Oracle actually uses a slightly different representation internally when working with dates in memory.
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
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- date型別型別
- CLUSTER內部結構的總結(1)
- CLUSTER內部結構的總結(2)
- CLUSTER內部結構的總結(3)
- CLUSTER內部結構的總結(4)
- Date型別和Regex型別型別
- LoadRunner內部結構
- 索引內部結構探索索引
- undo 事物內部結構
- DATA BLOCK內部結構BloC
- B樹索引的內部結構索引
- JavaScript內部物件和Date物件JavaScript物件
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數
- ORACLE NUMBER型別內部實現Oracle型別
- ora-600內部錯誤的型別型別
- Redis 內部資料結構Redis資料結構
- 【REDO】Oracle redo內部結構Oracle Redo
- DATA BLOCK內部結構圖BloC
- MySQL-InnoDB內部結構MySql
- CNN、RNN、DNN的內部網路結構有什麼區別?CNNRNNDNN
- Kafak探究之路- 內部結構小結
- Redis字串型別內部編碼剖析Redis字串型別
- 物件導向--內部屬性型別物件型別
- 3.6 Java內部類四種型別Java型別
- zt_ora-600內部錯誤的型別型別
- DATE型別INTERVAL分割槽型別
- DATE型別擷取到天的效率型別
- Redis 字串 內部資料結構Redis字串資料結構
- 【原創】MySQLProxy-內部結構MySql
- zt_undo 事物內部結構
- 深入理解Cassandra內部結構
- 探索Kafka消費者的內部結構Kafka
- Redis資料結構的內部編碼Redis資料結構
- dump Oracle資料庫的內部結構Oracle資料庫
- Cassandra的內部資料儲存結構
- 將timestamp型別轉換為date型別型別
- libdispatch 結構型別型別
- date、timestamp欄位型別型別