Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ORACLE-Internal-Date-Format
A copy of this was sent to jmodic_at_src.si (Jurij Modic) (if that email address didn't require changing) On Fri, 25 Dec 1998 16:47:06 GMT, you wrote:
>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)
>-------------------------------------------
>Typ=13 Len=8: 206,7,12,25,12,45,47,0
>
>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).
>
On your system thats what you do. On a reverse byte system -- you don't. You are looking at a 2 byte INT in an OS specific byte order. Go to another architecture and your math won't work :)
Type 12 is the DATE datatype used by the program interface and used for storing columns. It is ALWAYS 7 bytes long. Type 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Type 13 may NOT be used with BIND/DEFINE and is never returned by DESCRIBE.
Note that not only does SYSDATE return Typ=13 in a dump but to_date does as well:
SQL> select dump( to_date('01-Jan-1998','dd-mon-yyyy') ) from dual;
DUMP(TO_DATE('01-JAN-1998','DD-
But pl/sql BIND variables (as stated above about bind variables) do not:
SQL> declare
2 d date := sysdate;
3 begin
4 for x in ( select dump(d) d from dual ) loop 5 dbms_output.put_line( x.d); 6 end loop;
>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
Not really -- its the result of INTERNAL date functions that do this -- not dates that are not stored in the database (example above -- pl/sql variable D is not in a table but its type is 12, not 13. To_date and Sysdate -- internal functions returning dates return typ=13).
If you create a non-internal function to return a date it will be typ=12
SQL> desc foo
FUNCTION foo RETURNS DATE
SQL> select dump(foo) foo from dual;
FOO
it returns type 12, not 13. It is only internal date functions (including new_time, add_months, etc) that use typ=13. Don't count on the 8 length being consistent either. On my system for example (surprisingly)
SQL> select dump(add_months(sysdate,0)) from dual;
DUMP(ADD_MONTHS(SYSDATE,0))
>
[snip]
>
>TIA,
>
>Jurij Modic <jmodic_at_src.si>
>Certified Oracle7 DBA (OCP)
>================================================
>The above opinions are mine and do not represent
>any official standpoints of my employer
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
-- http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.Received on Fri Dec 25 1998 - 00:00:00 CST