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.
Subsecond Timestamp manipulation
Moderators: chulett, rschirm, roy
-
mjmagalsky
- Participant
- Posts: 5
- Joined: Wed Apr 18, 2007 2:38 pm
Just a shot in the dark, try
Code: Select all
TimestampFromSecondsSince(-.001000, FunneledData.TRANSACTION_TIME)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
mjmagalsky
- Participant
- Posts: 5
- Joined: Wed Apr 18, 2007 2:38 pm
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)