Re: TIMESTAMP precision and averaging INTERVAL

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 -- Received on Fri Nov 09 2007 - 10:50:41 CST