Tibco Businesswork, format timestamp in long to mm/dd/yyyy

I'm trying to publish a XML message using the tibco adb adapter which monitors a table. There is a column开发者_Go百科 in the table that stores a date as a long value in milliseconds- Is there a way we can convert this long value to a date as a part of the XML marshalling ?

-tia

You can translate the milliseconds to a datetime xml value with the following x-path:

tib:add-to-dateTime(tib:translate-timezone('1970-01-01T00:00:00Z',  tib:get-timezone-from-dateTime(current-dateTime())), 0, 0, 0, 0, 0, (<your input in ms> div 1000))

What this does is:

1) Find your timezone:

tib:get-timezone-from-dateTime(current-dateTime())

2) Move the base date (1970-01-01) to the correct timezone:

tib:translate-timezone('1970-01-01T00:00:00Z' <result above>)

3) Add the number of seconds in your input epoch:

tib:add-to-dateTime(<result above>, 0, 0, 0, 0, 0, (<your input in ms> div 1000))

Notice the downside to this solution is losing the milliseconds precision. The final datetime is in seconds.

If you need to keep the milliseconds in the final result, you are better off delegating this to an external Java library (as someone already suggested.) You can easily make a Java method available in the Designer mapper by importing it through a Java Custom Function resource.

I had to write a custom java process to get this done - Just in case someone finds it useful

I have ran into this quite a bit. We use the option in Adb to write all of the data to the other table. What you can do then do the following

Option 1 1. Change the data type of the other table to be varchar 2. Update the trigger to do e conversion for you 3. Update the scheme in bw. - this will make it so you cannot use the GUI anymore for updates to Adb

Option 2 An alternate patter that we use for Adb is to use Adb as a trigger for an action. So instead of using all the information from Adb we use the key. We do a callback to a stored procedure to retrieve the rest of it. This is where we fix the formatting issue.

So if you are faimiliar with doing a db call in bw the second option is by far the easiest and less error prone. We have been using Tibco for about 3 years and have found some limitation to the active database adapter. Behind the scenes it is just a table pooler with an XML serialized and places the messages on a queue/topic

Option 3 The other option I have used is to create a pretty complicated mapper in your BW process. Search for a space and replace it with a "t".
Hope this helps

This works for me, and taking the resulting date time without the 'Z' in the end and adding the milliseconds I have my precision back. Now just to work out the daylight saving time ...

concat( substring( tib:add-to-dateTime(tib:translate-timezone('1970-01-01T00:00:00Z', tib:get-timezone-from-dateTime(current-dateTime())), 0, 0, 0, 0, 0, ($Element/root/s_date_in_ms div 1000)) ,1,19) , ".", substring($Element/root/s_date_in_ms,12,3) )

2014-11-10T23:02:28.858

tib:add-to-dateTime('1970-01-01T00:00:00', 0, 0, 0, 0,0, (<<timeinmillisecond>> div 1000))