How to subtract 1 in milisecond part of timestamp

Database
Enthusiast

How to subtract 1 in milisecond part of timestamp

Hi All,

I have a requirement in which I need to make the timestamp field a unique one all the time.

I need to insert the data in TBL2 from the TBL1 and the timestamp should be lessesr than the lowest timestamp in TBL2. So that the new inserted row will have the lowest timestamp.

something like this:

Insert into tab1

select col1,col2,col3,(lowest timestamp from tab1)-1 mili second

from tab2

where conditions.

Thanks a lot for the help in advance.

Terankit

4 REPLIES
Junior Contributor

Re: How to subtract 1 in milisecond part of timestamp

insert into tab1

select col1,col2,col3,

(select min(timestamp) from tab1) - interval '0.000001' second

from tab2

If you want to insert multiple rows and each needs a unique timestamp:

(select min(timestamp) from tab1) - (row_number() over (order by whatever) * interval '0000 00:00:00.000001' day to second)

Dieter

Enthusiast

Re: How to subtract 1 in milisecond part of timestamp

thanks dnoeth.

terankit

Enthusiast

Re: How to subtract 1 in milisecond part of timestamp

Hi dnoeth,

One more thing. If the least timestamp is null in tab1  (for a perticular process type i am looking for) then the resultant timestamp will also be null. Can we put somekind of check so that whenever the timestamp is null it will put the timestamp as :

CAST(DATE||' 00:00:00.000000'  AS TIMESTAMP)(CHAR(26))

That means:

Insert into tab1

select col1,col2,col3,(lowest timestamp where process_type= "A" from tab1)-1 mili second

when this timestamp is null then

CAST(DATE||' 00:00:00.000000'  AS TIMESTAMP)(CHAR(26))

else the min timetsamp -1 mili second

from tab2

Thanks,

Terankit

Junior Contributor

Re: How to subtract 1 in milisecond part of timestamp

Simply use coalesce:

insert into tab1

select col1,col2,col3,

(select coalesce(min(timestamp), cast (current_date) as timestamp(6)) from tab1) - interval '0.000001' second

from tab2

Dieter