Database Reference
In-Depth Information
The century and year bytes (the 120,105 in the
DUMP
output) are stored in an excess-100 notation. You would have
to subtract 100 from them to determine the correct century and year. The reason for the excess-100 notation is support
of BC and AD dates. If you subtract 100 from the century byte and get a negative number, it is a BC date. For example:
EODA@ORA12CR1> insert into t (x) values
2 ( to_date( '01-jan-4712bc',
3 'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
EODA@ORA12CR1> select x, dump(x,10) d from t;
X D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
So, when we insert 01-JAN-4712BC, the century byte is 53 and 53 - 100 = -47, the century we inserted. Because it
is negative, we know that it is a BC date. This storage format also allows the dates to be naturally sortable in a binary
sense. Since 4712 BC is
less than
4710 BC, we'd like a binary representation that supports that. By dumping those two
dates, we can see that 01-JAN-4710BC is
larger
than the same day in 4712 BC, so they will sort and compare nicely:
EODA@ORA12CR1> insert into t (x) values
2 ( to_date( '01-jan-4710bc',
3 'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
EODA@ORA12CR1> select x, dump(x,10) d from t;
X D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
01-JAN-10 Typ=12 Len=7: 53,90,1,1,1,1,1
The month and day bytes, the next two fields, are stored naturally, without any modification. So, June 25 used a
month byte of 6 and a day byte of 25. The hour, minute, and second fields are stored in excess-1 notation, meaning
we must subtract 1 from each component to see what time it really was. Hence midnight is represented as 1,1,1 in the
date field.
This 7-byte format is naturally sortable, as you have seen—it is a 7 byte field that can be sorted in a binary fashion
from small to larger (or vice versa) very efficiently. Additionally, its structure allows for easy truncation, without
converting the date into some other format. For example, truncating the date we just stored (25-JUN-2005 12:01:00) to
the day (remove the hours, minutes, seconds) is very straightforward. Just set the trailing three bytes to 1,1,1 and the
time component is as good as erased. Consider a fresh table,
T
, with the following inserts:
EODA@ORA12CR1> create table t ( what varchar2(10), x date );
Table created.
EODA@ORA12CR1> insert into t (what, x) values
2 ( 'orig',
3 to_date( '25-jun-2005 12:01:00',
4 'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
