Re: oracle timestamp internal storage and precision

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Tue, 18 Dec 2012 19:15:32 -0700
Message-ID: <50D12344.9030509_at_gmail.com>

According to Oracle's documentation, at http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#i54330, 'DATE' types have a fixed size of 7 bytes, and TIMESTAMPS (without TZ) have "The size is 7 or 11 bytes, depending on the precision." Add stored TZ, and you go to 13 bytes.

With an encoding scheme such as Binary Coded Decimal, a digit can be stored in a Nybble, making 2 digits per Byte. (Oracle uses a modification to BCD.)

YY YY MM DD HH MI SS accounts for 7 bytes (2 digits, or a pair, per bytye) which is great for DATE and TIMESTAMP without fractional seconds.

The fractional seconds, according to the book, can range to 9 digits, which needs to fit into 4 bytes.. However, the BCD encoding would require 5 bytes for the 9 digits, so another scheme is likely. I note that the 9 digits '999999999' are 0x3B9AC9FF which fits nicely into 8 Nybbles, or 4 bytes.

All this can, of course, be verified by testing on an actual database

SQL> create table dates ( d date, t timestamp(9));

Table created.

SQL> insert into dates values (to_date('20120401 123456', 'YYYYMMDD HHMISS'),
   2* to_timestamp('20120401 123456.123456789', 'YYYYMMDD HHMISS.FF9'));

1 row created.

SQL> select dump(d), dump(t) from dates;

DUMP(D)

---

DUMP(T)

---

Typ=12 Len=7: 120,112,4,1,13,35,57
Typ=180 Len=11: 120,112,4,1,13,35,57,7,91,205,21

SQL> In which you can see the date portion is the same for TIMESTAMP and DATE, and the fractional seconds (123456789) seems to be captured in the last 4 bytes.

I'll let you investigate the offsets Oracle uses in the datetime portion and the different offsets in the fractional seconds portion to avoid leaving bytes with '00' which could be misinterpreted as 'NULL' or 'line end'. ;-)

/Hans

On 18/12/2012 5:49 PM, Walker, Jed S wrote:
> I could swear that a while back I read or saw a presentation from someone where they discussed how Oracle stores the internal timestamp. They said it was stored as a number and that in order to not expire to early (like UNIX time) the storage would, over time, sacrifice sub-second precision to extend the date range. I tried searching for this, but have found nothing. I wonder if I'm remembering wrong, or if maybe it just isn't something that has been published.
> Do any of the Oracle internal experts out there know?

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 19 2012 - 03:15:32 CET