Sunday, December 5, 2010

Oracle: Internal structure of "date" data type

Did some investigation on internal date data format:

A date in binary format contains seven bytes, as shown in table below:

BYTE          1    2     3   4    5      6      7
Meaning Century Year Month Day Hour Minute Second
Example     120  110    11  30   16     18      1


The century and year bytes are in an excess–100 notation.

Dates Before Common Era (BCE) are less than 100.

The era begins on 01–JAN–4712 BCE, which is Julian day 1.

For this date, the century byte is 53, and the year byte is 88.

The hour, minute, and second bytes are in excess–1 notation.

The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60.

If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).

You can use DUMP function to investigate this:

 SQL> create table t ( x date );

Table created.

SQL> insert into t values ( to_date( '03-nov-2010 3:17:01pm', 'dd-mon-yyyy hh:mi:sspm' ) );
1 row created.
SQL> commit; 


SQL> select dump(x) from t;
DUMP(X)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,110,11,3,16,18,2

No comments:

Post a Comment