Re: Millisecs in Oracle timestamp

On Mar 2, 1:50 pm, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> Anurag Varma schrieb:
>
>
>
> > 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
>
> Why convert if you can use interval literals?
>
> UPDATE threadstart
> SET startstamp = startstamp + INTERVAL '133' SECOND;
>
> Best regards
>
> Maxim

You are right ... Actually my update was wrong too. I was converting 133/86400 seconds .. you are converting 133 seconds. And OP wanted 133 seconds....

so interval '133' second ... would be equivalent to numtodsinterval(133,'second')
... just two different ways of writing it. Your method definitely is cleaner / better.

Anurag Received on Fri Mar 02 2007 - 13:12:55 CST