Time zone conversion in PI mapping

[engswee]

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.)

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.

Reference

Joda-Time

SimpleDateFormat

Wikipedia list of time zones