Re: Millisecs in Oracle timestamp

On 2 Mar, 19:12, "Anurag Varma" <avora..._at_gmail.com> wrote:
> 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

Guys Thanks for your help!

startstamp = startstamp + INTERVAL '133' SECOND works perfectly! Received on Mon Mar 05 2007 - 03:04:51 CST