Re: TIMESTAMP precision and averaging INTERVAL

On Fri, 09 Nov 2007 08:55:03 -0800, Mark D Powell <Mark.Powell_at_eds.com> wrote:

>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 --
>
>

Thanx! I figured it was platform dependent, but i wanted confirmation. The information on the accuracy of statements is quite interesting too.

B. Received on Fri Nov 09 2007 - 11:30:20 CST