RE: oracle timestamp internal storage and precision
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Dec 2012 16:45:40 +0000
Thanks Jared. I'm still curious if anyone knows the answer to my question?
Does Oracle's internal format sacrifice precision over time to avoid running
out of dates?
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Tuesday, December 18, 2012 9:23 PM
To: Walker, Jed S
Cc: oracle-l
Subject: Re: oracle timestamp internal storage and precision
On Tue, Dec 18, 2012 at 4:49 PM, Walker, Jed S
<Jed_Walker@xxxxxxxxxxxxxxxxx<mailto:Jed_Walker@xxxxxxxxxxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
