| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORACLE-Internal-Date-Format
On Tue, 22 Dec 1998 02:48:34 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:
>A copy of this was sent to Reinhard Wahl <wahl_at_zv.fhg.de>
>(if that email address didn't require changing)
>On Mon, 21 Dec 1998 19:12:28 +0100, you wrote:
>
>>Does anyone know how ORACLE stores Date-entries internal?
>>Where can I find it in the documentation?
>>
>
>The server concepts manual talks about it in the datatype section. the oci
>programmers manual goes into more depth. In short, it is a 7 byte field that
>stores the century, year in the century, the month, the day, the hour, the
>minute and seconds.
>
>More detail from the oci manual:
>...[SNIPPED QUOTE]...
>
>You can also use DUMP to inspect these values, for example:
>
>SQL> insert into t values
> > ( to_date( '03-nov-1992 3:17:01pm', 'dd-mon-yyyy hh:mi:sspm' ) );
>
>1 row created.
>
>
>SQL> select dump(x) from t;
>
>DUMP(X)
>-----------------------------------
>Typ=12 Len=7: 119,192,11,3,16,18,2
Up until now I belived that this "excess-100" notation stuf in the manuals was just the documentation bug. Now that I saw your post I forced myself to literaly repeat your example and have found I lived under the wrong impression how years are stored inside the first two bytes of the 7-bytes internal date storage. And what was the reason for this missunderstanding of mine?
When I've read about the oracle's internal storage of dates some years ago, I made a simple test to see I understand it correctly. I did something like the following:
SQL> select dump(sysdate) from dual;
DUMP(SYSDATE)
And to my surprise it turned out the first two bytes don't represent the century+year in the excess-100 notation. No way. So I concluded the documentation was wrong. With some trying I figured it out how to interpret the first two bytes of this output: if you multiply the second byte with 256 and add to first byte you get the year (206 + 7*256 = 1998).
But now I see the documentation is not wrong when it describes the internal format of *stored* dates, which is obviously different from the internal format of the dates that are *not actualy stored* in the tables. Eg
SQL> create table date_table (date_col date);
Table created.
SQL> insert into date_table(date_col) values (sysdate);
1 row created.
SQL> select dump(date_col) from date_table;
DUMP(DATE_COL)
SQL> select dump(sysdate) from date_table;
DUMP(SYSDATE)
In the later case there is also a strange code for datatype that DUMP function returns. I couldn't find anything about this type=13 in the manuals (date datatype is represented by the code 12). And also the length reported is 8 instead of 7 (I noticed the last byte is allways 0). I also see that (with non-stored dates) hours, minutes and seconds are obviously not represented in the "excess-1" notation (hours range from 0 to 23, minutes and seconds from 0 to 59).
So, can anybody explain why the DUMP function returns different results when dealing with dates that are not stored in tables compared to the ones that are actualy stored in the tables? And what does the type code 13 represent?
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Service Industries
>Reston, VA USA
TIA,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |