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

Oracle is converting timestamp to date when you try performing simple date calculation on it.

You have to convert the "seconds" to a timestamp type using numtodsinterval function.

See this:
10GR2> select a, to_char(b, 'HH24:MI:SS.FF') from xt;

         A TO_CHAR(B,'HH24:MI

---------- ------------------
         1 13:22:02.570798

10GR2> update xt set b = b - (133/86400) where a = 1;

1 row updated.

10GR2> select a, to_char(b, 'HH24:MI:SS.FF') from xt;

         A TO_CHAR(B,'HH24:MI

---------- ------------------
         1 13:19:49.000000

10GR2> truncate table xt;

Table truncated.

10GR2> insert into xt values (1,systimestamp);

1 row created.

10GR2> update xt set b = b - numtodsinterval(133/86400,'second') where a = 1;

1 row updated.

10GR2> select a, to_char(b, 'HH24:MI:SS.FF') from xt;

         A TO_CHAR(B,'HH24:MI

---------- ------------------
         1 13:28:50.361640

Anurag Received on Fri Mar 02 2007 - 12:33:40 CST