DSXchange

Posted: **Thu Apr 19, 2007 8:59 am**

I need to subtract one millisecond from a Timestamp in a Transform stage. However, TimestampFromSecondsSince is apparently only partially-compatible with subsecond times. For example, this derivation:
TimestampFromSecondsSince(-.001, FunneledData.TRANSACTION_TIME)
yields this result:
Input :2007-04-11 15:42:28.113000
Output:2007-04-11 15:42:27.999000
I expected:2007-04-11 15:42:28.112000

It appears to round down to the full second and then subtract the partial second. Any input or alternative suggestions are appreciated.

Note that no BASIC transforms are an option I'm told because we are using parallel jobs.

Posted: **Thu Apr 19, 2007 9:09 am**

Just a shot in the dark, try

Code: Select all

TimestampFromSecondsSince(-.001000, FunneledData.TRANSACTION_TIME)

Posted: **Thu Apr 19, 2007 11:30 am**

No luck with the -.001000 idea. The best I've come up with this cumbersome idea. The first part deals with even seconds and the second parses out the subsecond portion and performs arithmetic on it and then appends it back together. Yuk.

Code: Select all

If(Field(FunneledData.TRANSACTION_TIME,".",2,1) = "000000") then TimestampToString(TimestampFromSecondsSince(-.001, FunneledData.TRANSACTION_TIME), "%yyyy-%mm-%dd %hh:%nn:%ss.6") 
else
Left(TimestampToString(FunneledData.TRANSACTION_TIME, "%yyyy-%mm-%dd %hh:%nn:%ss"):".":Field(DecimalToString(StringToDecimal(Field(FunneledData.TRANSACTION_TIME,".",2,1))/1000000-.001000),".",2,1),26)

DSXchange

Subsecond Timestamp manipulation

Subsecond Timestamp manipulation