The Boomi datetime dilemma

The Boomi datetime dilemma

IMPORTANT

The dilemma is that you cannot prevent Boomi from converting the time if you want to work with explicit time-zones, like yyyy-MM-dd HH:mm:ssZ. You need to understand when and how Boomi converts the time to gain control of the process.

This is even more important because Boomi’s conversion depends on the time zone of the Atom. This means, for example, that the automatic summertime/wintertime changeover at a certain point in time can lead to changed and unforeseen execution results. To stress the importance even more, you need to know that Cloud Atoms run in UTC time-zone (as far as I have noticed it), while local Atoms normally don’t and disregarding the following relationships can lead to completely unpredictable behaviour of processes fi you move from Cloud to local or vice-vera.

I recommend reading the basics before you continue here.

Boomi Platform Behaviour

Let’s use the following view to explain Boomi Integration’s datetime behaviour:

IN-DateTime → Enter Platform | Processing | Leave Platform → OUT-DateTime

You can look at this like a mapping: Each value flows through the platform. A source field enters the platform, it is processed by (or simply mapped on) the platform (Functions pane) and it leaves the platform, to be assigned to a target field.

The Boom Platform time-zone

It is important to understand that a datetime, when it is on the platform:

• always has the following format yyyyMMdd HHmmss.SSS (no explicit time-zone)

• and it is in the ATOM local time-zone! (implicit Atom time-zone)

This means, that an incoming datetime with an explicit time-zone (like Z) is converted to the Atom’s local time-zone when it enters the platform. When a datetime leaves the platform the time is converted to the time-zone according to the target field’s specification.

Example

Imagine you have …

• a source field of format yyyy-MM-dd'T'HH:mm:ss'Z'

• and a database target profile with a datetime field.

Note: Database profiles specify a Date/Time type, only, there is no explicit format specification. Such fields require yyyyMMdd HHmmss.SSS as the input.

• The source field has a UTC time-zone specification

  • When the field enters the platform

    • it is shifted from UTC to the ATOM local time-zone and it is formatted into yyyyMMdd HHmmss.SSS. (If ATOM time-zone is Central-European-Summer-Time, a shift of +02hrs takes place.)

• The target field receives what comes from the platform (no target format specified - no conversion). So, the database will then no longer receive the UTC time but the shifted ATOM time-zoned datetime!

Investigations and further explanation

Let’s do some examples to understand the behaviour in detail:

FieldName	Format	Time-Zone
j.IN_DateTime.DateTime	yyyyMMdd HHmmss	no time zone
j.OUT_DateTime.DateTimeS	character	plain platoform format
j.OUT_DateTime.DateTimeZ	yyyy-MM-dd'T'HH:mm:ssZ	converted into UTC

j.IN_DateTime

{ "DateTime"  : "20230731 120000" }

j.OUT_DateTime

Different results depending in the Atom time-zone.

ATOM Time-Zone:  **CEST → UTC+2** 
{
  "DateTimeZ" : "2023-07-31T12:00:00**+0200**",
  "DateTimeS" : "20230731 120000.000"
}

Cloud ATOM Time-Zone:  **UTC**
{
  "DateTimeZ" : "2023-07-31T12:00:00**+0000**",
  "DateTimeS" : "20230731 120000.000"
}

We notice the following:

1. Even if we requesting any conversion, just a simple 1:1 mapping, the result depends on the ATOM’s time-zone.

2. DateTimeZ contains a time-zone which tells us, the IN datetime (12:00) was interpreted as a ATOM local time and the ATOM’s time-zone was added.

IMPORTANT

• If you change the format of a datetime field, time-zone shifts may take place, depending on the ATOM’s time-zone.

• DateTimeS (string format) has got a three digits milliseconds appendix.

I came to the following conclusion:

• When a datetime enters the platform

  • it is shifted into the ATOM’s local time-zone.

  • The format is turned into the ATOM internal format - with no explicite time-zone.

Note: You can use a Groovy script inside of the mapping to see that “20230731 120000.000” has become “Mon Jul 31 12:00:00 CEST 2023” after the datetime field entered the platform, for the ATOM that run in CEST time-zone!

• When a datetime leaves the platform

  • the format is adjusted according to the target field’s format specification

  • if the target field format requires a time-zone

    • the ATOM’s time-zone offset is applied

More examples, providing evidence

ATOM Time-Zone: CEST → UTC +2

IN	OUT	Character	G-Script
20230731 120000Z	2023-07-31T14:00:00+0200	20230731 140000.000	Mon Jul 31 14:00:00 CEST 2023
20230731 120000	2023-07-31T12:00:00+0200	20230731 120000.000	Mon Jul 31 12:00:00 CEST 2023

Passing in a UTC datetime

• We are passing 12:00 UTC into the platform.

• The platform recognizes the Z in the IN format and transforms the time to ATOM-local: 14:00 (see Character)

• The Groovy Script provides evidence this is the case: 14:00 CEST, which is 12:00 UTC.

• When the date leaves the platform it is converted from ATOM-local to UTC, by adding the ATOM’S time-zone: 14:00+0200 (= UTC 12:00)

Passing in a datetime without time-zone information

• We are passing 12:00 into the platform.

• The platform takes the date-time as-is: ATOM-local: 12:00 (see Character)

• The Groovy Script provides evidence this is the case: 12:00 CEST.

• When the datetime leaves the platform it is converted from ATOM-local to UTC, by adding the ATOM’S time-zone: 12:00+0200 (= UTC 10:00)

Cloud ATOM Time-Zone: UTC

Same input: please compare the output with the CEST ATOM!

In	OUT	Character	G-Script
20230731 120000Z	2023-07-31T12:00:00+0000	2023073 120000.000	Mon Jul 31 12:00:00 UTC 2023

Conclusion

The following is true when you are using fields of datatype datetime:

• A datetime that enters the platform

  • is shifted into the ATOM’s local time-zone

    • if the format specifies a time-zone: ‘Z’ or Z

  • the format is changed to yyyyMMdd HHmmss.SSS

• A datetime that leaves the platform

  • is shifted from the ATOM’s local time-zone to the time-zone specified in the target format. For example: ‘Z’ or Z.

  • If the target field does not specify a target format (like for database profiles, for example)

    • the datetime leaves the platform in the ATOM’s local time-zone in the format yyyyMMdd HHmmss.SSS