Re: Millisecs in Oracle timestamp

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 02 Mar 2007 19:50:16 +0100
Message-ID: <45E871E8.7040204@arcor.de>

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 Received on Fri Mar 02 2007 - 12:50:16 CST