Re: oracle timestamp internal storage and precision

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 18 Dec 2012 20:22:38 -0800
Message-ID: <CAORjz=OrxywQhGvOzk=HWdM9BvK3L9EKYCjj_Qr-PMOmWB0J1w_at_mail.gmail.com>

On Tue, Dec 18, 2012 at 4:49 PM, Walker, Jed S <Jed_Walker_at_cable.comcast.com
> 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?
>
>
>

I was going to point you to an article I wrote some time ago on oracle dates.

The website it used to appear on has been deprecated unfortunately.

Following is the location of a zip file that contains an article on oracle dates, and oracle date internals.
Code is included.

http://jaredstill.com/downloads/oracle_dates.zip

One interesting thing you will learn is the difference between to_date, sysdate and dates stored in a table.

An exercise for the reader - see if the those differences remain the same in 11g.
Please report back to the list if you do. :)

There are a couple things not covered in the article: * timestamps
* trace files

It is possible to parse dates out of trace files (I've used that) While it is not something you would often need to do (maybe never) it can be useful in some instances.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

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

Received on Wed Dec 19 2012 - 05:22:38 CET