Re: Millisecs in Oracle timestamp

On Mar 2, 1:09 pm, "paul.sprat..._at_gmail.com" <paul.sprat..._at_gmail.com> wrote:
> Hi
>
> I really need help - got a big delivery on Monday and having a
> nightmare.
>
> I need to do something really simply but for some reasons I cannot
> verify my results because I cannot see the millisecs for a timestamp.
> I'm using Aqua Studio and have set config settings to see millisecs
>
> I want to add x secs to a timestamp and I use
>
> UPDATE THREADSTART
> SET STARTSTAMP=STARTSTAMP - (133/86400)
> WHERE RUNID = 400
>
> This should add 133 seconds to the startstamp which it does.
>
> However this timestamp records millisecs and I cannot see the
> millisecs after I execute the above - well I see that they are set to
> zero.
>
> I use this to view the records before I can see the millisecs -
> afterwards the are set to zero
>
> SELECT startstamp, to_char(startstamp,'HH24:MI:SS.FF')
> FROM THREADSTART
> GO
>
> However - if I try to update the timestamp by adding millisecs it
> doesnot work.
>
> Table below
>
> Thanks for the help!
>
> CREATE TABLE THREADSTART (
> RUNID NUMBER(20,0) NOT NULL,
> INSTANCEID NUMBER(20,0) NOT NULL,
> STARTSTAMP TIMESTAMP(6) NOT NULL,
> STOPSTAMP TIMESTAMP(6) NULL,
> PRIMARY KEY(RUNID,INSTANCEID)
> )
> GO

You say adding but your code shows subtracting so which way are you trying to move the timestamp value? You are mixing timestamp and date data types so since dates only work to the nearest second I suspect you are zapping the milliseconds to zero.

Try casting the date second calculation to a timestamp.

HTH -- Mark D Powell -- Received on Fri Mar 02 2007 - 12:22:49 CST