Re: how to store timestamp in oracle 8.1.7?

TurkBear,

> Please IGNORE this poster is INCORRECT ; the Oracle DATE type is,
> in fact, a TIMESTAMP - it has date and time ( to the second )
> and you use it exactly like > a timestamp as long as you enter the data
> correctly;
> ....
> ....
> Please be sure and test before posting...

Original poster was inquiring about TIMESTAMP as included in 9i...which includes fractional part of a second. DATE datatype gives you a point-in-time values (HH:MI:SS) which can be used to see e.g. when a particular row was inserted/updated. *BUT* if you want to precisely determine which of the two events occured first, then you use TIMESTAMP (HH:MI:SS:000000000) with fractional second separator. There are number of reasons why fractional second need to be used as a time separator. Why else do you think Oracle included this long awaited true TIMESTAMP feature in 9i?

Please read the original post clearly and how my post relates to it. Also look at 9i features and difference between a DATE datatype (in 8i) and a precise fractional second TIMESTAMP datatype (in 9i) before hastily responding.

Brian,

> The major difference between DATE and TIMESTAMP is that DATE stores day
> and time with a precision to the second. TIMESTAMP will let you store a
> much finer precision, i.e. fractions of a second (along with the date).

Correct. Original poster inquired particulraly about TIMESTAMP and not just DATE/TIME which he correctly distinguished as a feature *only* present in 9i and not the earlier versions. e.g.

DECLARE
   timestamp_ TIMESTAMP(3);
BEGIN
   timestamp_ := '1999-06-22 07:48:53.275';    ...
END;
> Why use VARCHAR2 and/or NUMBER to store dates and times? It's just too
> much work.

If you absolutely have to store fractional seconds in 8i or before, then you have to store them either as a NUMBER or VARCHAR2. It is not a preffered way but this is the only workaround because for sure you cannot store fractional seconds in DATE datatype.

> And then you want to throw Java into the equation?!?!?

Easiest way in 8i to generate a correct TIMESTAMP with fractional seconds is thru java.sql.Timestamp. The reason to use java to generate a fractional second TIMESTAMP is because Oracle has provided JVM in the database and someone already has done the work. If you want to use some other method to accomplish this... then you may use that.

Regards
/Rauf Sarwar Received on Thu Sep 26 2002 - 21:35:09 CDT