Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORACLE-Internal-Date-Format

Re: ORACLE-Internal-Date-Format

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/12/25
Message-ID: <3688c60c.14291930@192.86.155.100>

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-



Typ=13 Len=8: 7,206,1,1,0,0,0,0

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;

  7 end;
  8 /
Typ=12 Len=7: 119,198,12,25,13,11,52

>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



Typ=12 Len=7: 119,198,12,25,13,14,56

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))



Typ=13 Len=7: 7,206,12,25,12,15,27

>

[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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US