Subtract 1 sec from a timestamp (2005-06-22 13:51:06)

[vinaymanchinila]

Hi,
I need to load 2 columns, one with the Sysdate timestamp( I am using DSJobStartTimeStamp) , and the second with the timestamp -1sec.

Do I need to convert the value into internal form, subtract 1 sec from the value and again convert to oracle format or is there a better way to do it.
Thanks,

[lshort]

You instincts are correct.
That is how you do it.

[1stpoint]

In Oracle SQL you could simply create a "derived" column in your Source OCI stage:

This is how to subtract 1 second:

(SYSDATE - (1/(1*24*60*60)))

[vinaymanchinila]

Well I need to do it in DataStage, and looking for the conversion functions for the timestamp, as all I see there are conversin functions for dates only !

[vinaymanchinila]

Hi got the logic:

OCONV((ICONV(DSJobStartTimestamp[1,10],"D-YMD[4,2,2]")) , "D4-YMD[4,2,2]") :
" " :
OCONV((ICONV(DSJobStartTimestamp[12,8],"MTS")-1), "MTS")


The first peice takes the incomming date and converts it into internal format and then back to o/p format

The second peice converts the time into internal, subtracts 1 sec and converts back into o/p format.

Thanks,

[ray.wurlod]

You have a problem if the incoming timestamp is 2005-06-22 00:00:00 for which the result should be 2005-06-21 23:59:59.

Create a stage variable or routine to handle this boundary condition.

[vinaymanchinila]

Hi Ray,
If I create a routine, what logic should I put in to avoid getting

"2005-06-22 -00:00:01" with the negative sign !
Thanks a bunch for catching it

[vinaymanchinila]

Hi Ray,
Used a stage variable which says if the time value is "-00:00:01" then "23:59:59" , I hope that should catch it, are there any more scenarios that i need to consider , like the date peice? (I am subtracting seconds so I think it would not effect the date)

Thanks,

[ray.wurlod]

If you're going back from 00:00:00, then you need to subtract one day from the date.

Code: Select all

Oconv(Iconv(Left(InLink.Timestamp,10),"DYMD")-1,"D-YMD[4,2,2]")