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

Home -> Community -> Usenet -> c.d.o.misc -> Re: TIMESTAMP precision and averaging INTERVAL

Re: TIMESTAMP precision and averaging INTERVAL

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 09 Nov 2007 08:55:03 -0800
Message-ID: <1194627303.631304.273900@o38g2000hse.googlegroups.com>


On Nov 9, 11:50 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Nov 9, 9:56 am, Brian Tkatch <N/A> 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 -- Received on Fri Nov 09 2007 - 10:55:03 CST

Original text of this message

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