DATE Type-Datatypes
The DATE type is a fixed-width 7-byte date/time datatype. It will always contain the seven attributes of the century, the year within the century, the month, the day of the month, the hour, the minute, and the second.
Oracle uses an internal format to represent that information, so it is not really storing 20, 05, 06, 25, 12, 01, 00 for June 25, 2005, at 12:01:00. Using the built-in DUMP function, we can see what Oracle really stores:
$ sqlplus eoda/foo@PDB1
SQL> create table t ( x date );Table created.
SQL> insert into t (x) values( to_date( ’25-jun-2005 12:01:00′, ‘dd-mon-yyyy hh24:mi:ss’ ) );1 row created.
SQL> select x, dump(x,10) d from t;X D
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
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:
SQL> insert into t (x) values( to_date( ’01-jan-4712bc’, ‘dd-mon-yyyybchh24:mi:ss’ ) );1 row created.
SQL> 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:
SQL> insert into t (x) values ( to_date( ’01-jan-4710bc’, ‘dd-mon-yyyybchh24:mi:ss’ ) );1 row created.
SQL> 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 afresh table, T, with the following inserts:
SQL> create table t ( what varchar2(10), x date ); Table created.
SQL> insert into t (what, x) values ( ‘orig’, to_date( ’25-jun-2005 12:01:00′, ‘dd-mon-yyyy hh24:mi:ss’ ) ); 1 row created.
SQL> insert into t (what, x)select ‘minute’, trunc(x,’mi’) from tunion allselect ‘day’, trunc(x,’dd’) from tunion allselect ‘month’, trunc(x,’mm’) from tunion allselect ‘year’, trunc(x,’y’) from t; 4 rows created.
SQL> select what, x, dump(x,10) d from t; WHAT X D
orig 25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
minute 25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
day 25-JUN-05 Typ=12 Len=7: 120,105,6,25,1,1,1
month 01-JUN-05 Typ=12 Len=7: 120,105,6,1,1,1,1
year 01-JAN-05 Typ=12 Len=7: 120,105,1,1,1,1,1
To truncate that date down to the year, all the database had to do was put 1s in the last 5 bytes—a very fast operation. We now have a sortable, comparable DATE field that is truncated to the year level, and we got it as efficiently as possible.