Originally posted by: jdavee
We had a need to generate a UTC, GMT, Zulu time string from systems running in multiple time zones. We couldn't find an existing function in the docs or the WTX Forum, so after some testing we came up with this one.
It takes no parameters and relies on the system clock to properly report the current time zone. In testing we changed the zone on the PC to zones around the globe and it worked properly, including for those in 30 minute zones like Newfoundland.
We did note that in order for the zone change on the PC to be effective we had to stop and restart Design Studio. This brought up the issue of running this in Launcher during Daylight Savings Time changes. Will the Launcher invocation of the map properly manage DST if the launcher has not been bounced? Time will tell (ha ha)...
Anyway, for anyone to use, test, beat up, or otherwise poke holes, here's the function. The only element to change is the time format at the end - modify to the desired format as needed.
FROMDATETIME( ADDHOURS( ADDMINUTES( CURRENTDATETIME(), 0 - TEXTTONUMBER( mid(FROMDATETIME(CURRENTDATETIME(),
"{+/-ZZZZ}"), 1, 1) + right(FROMDATETIME(CURRENTDATETIME(),
"{+/-ZZZZ}"), 2) ) ), 0 - TEXTTONUMBER( mid(FROMDATETIME(CURRENTDATETIME(),
"{+/-ZZZZ}"), 1, 3) ) ),
"{CCYYMMDDHH24MMSS}" )
Gory Details: The function
FROMDATETIME(CURRENTDATETIME(), "{+/-ZZZZ}") returns the offset with UTC from the current system clock. The string returned looks like this:
-0500 EST
+0300 Mid Europe
-0430 Newfoundland
There is always a + or - sign, two digits for hour, two digits for minutes.
The
mid() functions construct two data strings, one for hours and one for minutes. Both will have the leading sign, so
-0430 produces
-04 for hours and
-30 for minutes.
The offset, however, is from the perspective of the local system. In other words, -0500 for EST means that Eastern Standard Time is 5 hours behind UTC.
To get to UTC from this offset we need the inverse value. Thus the
0 - (zero minus) part of the formula when using addhours() and addminutes().
The result is to:
1. Get current time
2. Get the UTC offset
3. Get offset hours and minutes as numbers
4. Inverse the hours and minutes by subtracting from zero
5. Add to the current time
0 - (-05) becomes
+05, so five hours are added to the current EST time to get UTC.
0 - (+03) becomes
-03, so three hours are subtracted from the current Mid-Europe time to get UTC.
#IBM-Websphere-Transformation-Extender#DataExchange#IBMSterlingTransformationExtender