ESQL time zone mapping functions

In conjunction with the Channel.TIMEZONE property, the following two functions are provided for interpreting time stamps from third parties that do not use a UTC+offset naming convention for time stamps.

Time zone.recastTimestampToBrokerTZ()

This function takes a time stamp and a time zone (usually the Channel.TIMEZONE) and casts it to the time zone of the broker. Time stamps must be cast to the time zone of the broker if the database is to be maintained in UTC. For more information, see Time zone considerations.

recastTimestampToBrokerTZ(IN tsIn TIMESTAMP, IN cCorrectTZ CHAR) RETURNS TIMESTAMP

Time zone.formatTSinTimezone()

This formats a time stamp in the brokers time zone to the specified time zone and format, resulting in a character string. Patterns for format strings can be formulated using the table in SimpleDateFormat (ICU4J 59.1). In addition to these formats, the format 'I' has been mapped to match the ESQL format 'I'. For example: yyyy-MM-dd-'T'HH:mm:ss.SSSXXX.

formatTSinTimezone(IN tsIn TIMESTAMP, IN cNewTZ CHAR, IN cFormat CHAR) RETURNS CHAR

Example 1

The broker is running in GMT-02:00 and receives a message with a time stamp without an explicit UTC offset, so the brokers offset is applied by default. We know that the source of these messages is in London and sends messages in its local time, so we set the Channel.TIMEZONE to "Europe/London" in the channel configuration. To correctly persist the time stamps in this message, the time stamps need to be converted to the time zone of the broker as follows;

SET tsPaymentDueTime = Time zone.recastTimestampToBrokerTZ(tsPaymentDueTime, Environment.PMP.Variables.Channel.TIMEZONE);

Example 2

Messages are to be sent to an external party who requires that the time stamps in the message are in the time zone of London and in a specified format, f ("12:08 PM, Fri, December 12, 2014" ). To do this, the Channel.TIMEZONE is configured to "Europe/London", and the required format string is calculated from the link above. The output string is then created as follows;

DECLARE cPaymentDueTime CHAR Time zone.formatTSinTimezone(tsIn, 
                                                              Environment.PMP.Variables.Channel.TIMEZONE, 
                                                              'hh:mm aaa, EEE, MMM d, yyyy');