Skip to Content
Author's profile photo Eng Swee Yeoh

Time zone conversion in PI mapping

Introduction

I had a requirement recently that involved handling of time zone conversion between the sender and the receiver systems. I had hoped that this was achievable by the standard mapping functions in PI, however there were certain limitations. Searching on SCN did not yield any suitable solutions, so I turned to good old Mr Google. I soon found out that Joda-Time was the de facto standard for handling date/time in Java prior to Java SE 8, as the date/time classes before that were considered poor.

After a bit of fiddling with the Joda-Time classes in a mapping UDF, I finally got it to work according to my requirement. In the section below, I will detail the solution I came up with as well as the options available when dealing with time zone conversion in PI.

Option 1 – Converting to local time zone

For conversions from other time zones to the local time zone (on the PI server), it can be achieved by the standard DateTrans function of the Date group. The input/output date formats in DateTrans are based on the Java’s SimpleDateFormat which caters for time zone related letters (z, Z, X) in the pattern.

In the following example, the input timestamp in Pacific Standard Time (PST stated in general time zone format z) is converted a timestamp in local server timezone GMT+8 (+0800 stated in RFC 822 time zone format Z.)

/wp-content/uploads/2015/02/datetrans_651667.png

Option 2 – Converting from any time zone to any other time zone

Since option 1 does not cater for conversions to time zones other than the local time zone, it had to be handled by a custom mapping UDF. Following is the source code of the UDF.

Prerequisite:

Download the library files for Joda-Time from Joda-Time’s repository.

Import JAR file into an Imported Archive object.

Add Imported Archive in Archives Used section of Message Mapping or Function Library.

Import statements for UDF


import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

Public method with Execution Type = ‘Single Values’, having 1 String input argument and 3 configurable parameters.


@LibraryMethod(title="convertTimeZone", description="Convert input time from one timezone to another",
        category="FL_DateTime", type=ExecutionType.SINGLE_VALUE)
public String convertTimeZone (
  @Argument(title="Input timestamp")  String timestamp,
  @Parameter(title="Timestamp format")  String format,
  @Parameter(title="From timezone")  String fromTZ,
  @Parameter(title="To timezone")  String toTZ,
   Container container)  throws StreamTransformationException{
  // ----------------------------------------------------------
  // Convert input time from one timezone to another
  // - utilizes Joda Time libraries
  // ----------------------------------------------------------
  // timestamp - input timestamp
  // format - pattern format of input and output timestamp similar allowed patterns below
  //        - http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
  // fromTZ - from timezone
  // toTZ   - to timezone
  //        - expected timezone in long format timezone ids listed in following site
  //        - http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
  // ----------------------------------------------------------
  DateTimeFormatter formatter = DateTimeFormat.forPattern(format);
  DateTimeZone originalTZ = DateTimeZone.forID(fromTZ);
  DateTime fromDateTime = new DateTime(DateTime.parse(timestamp, formatter), originalTZ);
  DateTime toDateTime = fromDateTime.withZone(DateTimeZone.forID(toTZ));
  return formatter.print(toDateTime);
}

The UDF expects the from/to time zone in long ID format (available in the TZ column of the Wikipedia site listed in the Reference section.)

In the following example, the input timestamp is converted from the Asia/Kuala_Lumpur time zone to the timestamp in UTC.

/wp-content/uploads/2015/02/udf_651666.png

Reference

Joda-Time

SimpleDateFormat

Wikipedia list of time zones

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dipen Pandya
      Dipen Pandya

      Hi Eng,

      Thanks for sharing.

      Quite Useful 🙂 .

      BR,

      Dipen.

      Author's profile photo Former Member
      Former Member

      Hi Eng,

      i used the code as you mentioned but got the below error, can you please help me out with this error

      Author's profile photo Anupam Ghosh
      Anupam Ghosh

      Hi Eng,

                     Well Written UDF.

      Regards

      Anupam

      Author's profile photo Uday Suvvada
      Uday Suvvada

      Hi Eng,

      UDF was working fine, but what about incase if we have to add the Daylight Savings time.

       

      Regards

      Uday Suvvada

      Author's profile photo Acram Shendi
      Acram Shendi

      Good Morning Gurus,

      I have implemented the UDF on SAP PO. It works fine, for example for Europe/Berlin to Etc/UTC but it does not work the other way around (so: Etc/UTC -> Europe/Berlin).

      Any ideay why this could not work? Parameters are:

      - format: yyyyMMddHHmmss

      - from time zone: Etc/UTC

      - to time zone: Europe/Berlin

      Any idea is highly appreciated.

      Best Regards

      Acram

      Author's profile photo Ivan Kurilov
      Ivan Kurilov

      Hi, this code work for your example

      @LibraryMethod(title="convertedTimeZone", description="Convert input time from one timezone to another",
              category="FL_DateTime", type=ExecutionType.SINGLE_VALUE)
      public String convertTimeZone (
        @Argument(title="Input dateTime")  String dateTime,
        @Parameter(title="Input dateTime format")  String strFormatInb,
        @Parameter(title="Output dateTime format")  String strFormatOut,
        @Parameter(title="From timezone")  String fromTZ,
        @Parameter(title="To timezone")  String toTZ,
         Container container)  throws StreamTransformationException{
      
      // ----------------------------------------------------------
        // Convert input time from one timezone to another
        // ----------------------------------------------------------
        // dateTime - input dateTime
        // strFormatInb - pattern format of input timestamp similar allowed patterns below
        //        - http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
        // strFormatOut - pattern format of output timestamp similar allowed patterns below
        //        - http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
        // fromTZ - from timezone
        // toTZ   - to timezone
        //        - expected timezone in long format timezone ids listed in following site
        //        - http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
        // ----------------------------------------------------------
              String result = "";
              SimpleDateFormat inputFormat = new SimpleDateFormat(strFormatInb);
              SimpleDateFormat outputFormat = new SimpleDateFormat(strFormatOut);
              try {
                  inputFormat.setTimeZone(TimeZone.getTimeZone(fromTZ));
                  Date date = inputFormat.parse(dateTime);
      
                  outputFormat.setTimeZone(TimeZone.getTimeZone(toTZ));
                  result = outputFormat.format(date.getTime());
      
              } catch (Exception e){
                  e.printStackTrace();
              }
      
              return result;
      }
      

       

      Example%20Time%20Zone

      Example Time Zone

      Author's profile photo Jens Schwendemann
      Jens Schwendemann

      Stumbled upon this only now. Great information and enjoyable read as always. Many thanks