Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news-out.readnews.com!postnews3.readnews.com!nr3.newsreader.com.POSTED!not-for-mail From: Brian Tkatch Newsgroups: comp.databases.oracle.misc Subject: Re: TIMESTAMP precision and averaging INTERVAL Date: Fri, 09 Nov 2007 12:30:20 -0500 Message-ID: References: <6gs8j3lep9sfdi4ubvmnbv2jude5r0lodl@4ax.com> <1194627041.341432.255440@o38g2000hse.googlegroups.com> <1194627303.631304.273900@o38g2000hse.googlegroups.com> X-Newsreader: Forte Agent 4.2/32.1118 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 100 Organization: NewsReader.Com Subscriber NNTP-Posting-Host: db2dd6bf.nr3.newsreader.com X-Trace: DXC=GX9TQT6mBL=8>O8N3G^:67OA8_@f\K_i2RPb4[mF?JAZKcU;KCAhI03QnY wrote: >On Nov 9, 11:50 am, Mark D Powell wrote: >> On Nov 9, 9:56 am, Brian Tkatch wrote: >> >> >> >> >> >> > Oracle 9.2.0.8.0 >> >> > 1) Oracle has a precision on TIMESTAMP with a default precision of 6. >> > Selecting 9 always returns three more zeroes. Why? >> >> > 09:45:35> SELECT SYSTIMESTAMP(9) FROM Dual; >> >> > SYSTIMESTAMP(9) >> > --------------------------------------------------------------------------- >> > 09-NOV-07 09.46.44.186794000 AM -05:00 >> >> > 09:46:44> / >> >> > SYSTIMESTAMP(9) >> > --------------------------------------------------------------------------- >> > 09-NOV-07 09.46.45.146996000 AM -05:00 >> >> > 09:46:45> / >> >> > SYSTIMESTAMP(9) >> > --------------------------------------------------------------------------- >> > 09-NOV-07 09.46.45.658828000 AM -05:00 >> >> > 2) I have a TABLE i am using for profiling (while debugging): >> >> > CREATE TABLE Profile >> > ( >> > Id INT, >> > What VARCHAR2(64), >> > Line INT, >> > When TIMESTAMP DEFAULT SYSTIMESTAMP, >> > Diff INTERVAL DAY TO SECOND, >> > CONSTRAINT P1_Id_NN CHECK(Id IS NOT NULL), >> > CONSTRAINT P1_Id_PK PRIMARY KEY (Id) >> > ); >> >> > The Id is put in by a TRIGGER/SEQUENCE pair, and Diff is an UPDATE: >> >> > UPDATE >> > Profile A >> > SET >> > Diff = When -(SELECT When >> > FROM Profile B >> > WHERE B.Id = A.Id -1); >> >> > I want to get the average interval for similar Lines (across "What"s), >> > something like: >> >> > SELECT >> > Line, >> > AVG(Diff) >> > FROM >> > Profile >> > GROUP BY >> > Line; >> >> > But, obviously, AVG() does not work on INTERVALs. Is there a built-in >> > method for averaging INTERVALs, or would i need to calculate the >> > seconds and microseconds myself. And, if the latter, does INTERVAL >> > even make sense? >> >> > B. >> >> The fractional time portion of the timestamp data type is platform >> dependent. Different hardware platforms only support n digits so the >> same Oracle code on a Linux box may produce a different value from a >> Sun box which in turn could be different from what is returned under >> IBM's AIX. >> >> In other words it is a hardware timer availability issue. Oracle uses >> what the vendor provides. >> >> HTH -- Mark D Powell --- Hide quoted text - >> >> - Show quoted text - > >PS - There is also the issue that the default is 6 digits of accuracy >so even on a platform that can provide 9 digits of accuracy if the >insert into the table defaulted to six digits then you can only >retrieve the value that was stored. > >HTH -- Mark D Powell -- > > Thanx! I figured it was platform dependent, but i wanted confirmation. The information on the accuracy of statements is quite interesting too. B.