About

Database and Flow

Database and Flow

  • SQL Script returns datetime or datetime2

    • datetime2 is unnecessary because it has a higher precision on the milliseconds which is cut-off by the platform anyway.

    • The returned datetime does not contain an explicit time-zone (no Z, no offset).

  • The database profile gets the returned datetime without any time-shifting.

    • Because of the missing time-zone

    • d.Profile value = SQL response.

  • By definition the datetime value is UTC!

  • The database profile always uses Date/Time data type to ensure a normalized data exchange with the SQL script, not relying on any character formating.

Map UTC-Z to a database profile
import com.boomi.execution.ExecutionUtil
import java.text.SimpleDateFormat

final String SCRIPT_NAME = "msgUTC_Z_TO_AtomDT"
inal _logger = ExecutionUtil.getBaseLogger()
_logger.finest('>>> Script start ' + SCRIPT_NAME)

/* **************************************************************************
    A Map Script that converts up to three Z formatted UTC dates to ATOM 
    default format without chaning the time-tone.

    IN:     inDateTimeZ1..Z3  ISO formatter UTC datetime
            Normally coming from a JSON profile with a datetime field
            formatted as "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'".

    OUT:    DateTime1..3  (output is always character)
			The UTC datetime formatted in the ATOM default format.
		    Formatting only, not time-zone shift.
		  
	Note: Depending on format of the Inputs:
    Character or Date/Time we get a different format in the logs,
    with inbound Map from JSON profile: 
    "CreatedUTC": "2023-07-28T13:20:51.525Z"
    > inDateTimeZ1 (Character) : 20230728 142051.525
    > inDateTimeZ1 (Date/Time) : Fri Jul 28 14:20:51 BST 2023
    --------------------------------------------
    2022-08-02  msc -   Created
************************************************************************** */

_logger.info('IN_1: ' + inDateTimeZ1 )   
_logger.info('IN_2: ' + inDateTimeZ2 )   
_logger.info('IN_3: ' + inDateTimeZ3 )   

// *******************************************************
// The incoming DateTime is always ATOM local time-zone!
// *******************************************************

// Target format for conversion
final SimpleDateFormat targetTimeZone = new SimpleDateFormat("yyyyMMdd HHmmss.SSS") 
// Target time-zone for conversion
targetTimeZone.setTimeZone(TimeZone.getTimeZone("UTC")) 

if( inDateTimeZ1 != null && inDateTimeZ1 != "") 
{
  DateTime1 = targetTimeZone.format( inDateTimeZ1)
}

if( inDateTimeZ2 != null && inDateTimeZ2 != "") 
{
  DateTime2 = targetTimeZone.format( inDateTimeZ2)
}

if( inDateTimeZ3 != null && inDateTimeZ3 != "") 
{
  DateTime3 = targetTimeZone.format( inDateTimeZ3)
}

_logger.info('OUT_1: ' + DateTime1 ) 
_logger.info('OUT_2: ' + DateTime2 ) 
_logger.info('OUT_3: ' + DateTime3 )

Map UTC-Z to a database profile
/// *************** COPY AND PASTE FROM HERE *****************
import com.boomi.execution.ExecutionUtil
import java.text.SimpleDateFormat

final String SCRIPT_NAME = "msgAtomDT_TO_UTC_Z"
inal _logger = ExecutionUtil.getBaseLogger()
_logger.finest('>>> Script start ' + SCRIPT_NAME)

/* **************************************************************************
    A Map Script that converts up to three Date/Time values 
		into a Z formatted UTC date.

    IN:     inDateTime1  Date/Time
					  inDateTime2  Date/Time
						inDateTime3  Date/Time
			Any "plain" datetime from the platform, so called: 
			ATOM-DateTime.
				Format: *yyyMMdd HHmmss.SSS*
				Value : ATOM local time-zone

    OUT:    DateTimeZ1..3  (output is always character)
			The datetime in ISO format ("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"), 
			containing the same value as the incoming datetime.
		  Formatting only, not time-zone shift.
    --------------------------------------------
    2022-08-02  msc -   Created
************************************************************************** */

/*
_logger = ExecutionUtil.getBaseLogger()
_logger.info('IN_1: ' + inDateTime1 )   // IN_1: Fri Jul 28 14:20:51 BST 2023
_logger.info('IN_2: ' + inDateTime2 )   // IN_2: Fri Jul 28 14:20:51 BST 2023
_logger.info('IN_3: ' + inDateTime3 )   //IN_3: null
*/

// For performance reasons we allow three conversions in a row.
final SimpleDateFormat utcISO = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")

// The incoming DateTime is always ATOM local tome-zone.
if( inDateTime1 != null && inDateTime1 != "") 
{
    Date dt1 = inDateTime1
    DateTimeZ1 = utcISO.format( dt1)
}

if( inDateTime2 != null && inDateTime2 != "") 
{
    Date dt2 = inDateTime2
    DateTimeZ2 = utcISO.format( dt2)
}

if( inDateTime3 != null && inDateTime3 != "") 
{
    Date dt3 = inDateTime3
    DateTimeZ3 = utcISO.format( dt3)
}

Observations

  • d.INC.DynUpdate: 20230728 142051.525

  • d.Get Incident:

SQL Return DataType
DB Connector DataType
Boomi Response: DB Profile
Map to j.Profile

DateTime2

Character

2023-07-28 14:20:51.5250000

DateTime2

Date/Time

20230728 142051.525

2023-07-28T13:20:51.525Z

datetimeoffset(3)

Character

2023-07-28 14:20:51.525 +00:00

datetimeoffset(3)

Date/Time

20230728 152051.525

2023-07-28T14:20:51.525Z

Boomi Response Comments

  1. 1400 is returned - this is 1:1 to what was written, however in the format specified by the DB (→ character type field)

  2. The datetime 1400 that was written to the DB is returned in the well defined Boomi Internal Format. This is what we want!

  3. The datetime is returned with an offset +00 so that the platform could interpret this as UTC. However, because of datatype Character no transformation takes place and we see the DB returned datetime.

  4. The datetime is (internally) returned to the DB connector with an offset +00 so that the platform does interpret this as UTC. The target database profile field type is set to Date/Time, and the platform shifts the returned UTC date (1400) to the ATOM time-zone (+1): 1500 on the profile.

Map to j.Profile comments

In any case, the DB profile DateTime is interpreted as ATOM local and a time-shift will take place.

Surprising to notice that the shift will also take place when using the Date Format function in a mapping.

Mapping Date Format does time-zone shifting, too!

There is actually no difference between a direct assignment from d.Profile Date/Type to j:Profile DateTime of ‘Z’ type and a date format shape in between.

Date Format shapes between date-time fields are unnecessary!

Mappings

If the target profile specifies a DateTime field of format yyyy-MM-dd'T'HH:mm:ss.SSS'Z' you cannot send a value of 2023-07-28T14:20:51Z to it (DateTimeZ Script output): Error Formatting Date '2023-07-28T14:20:51Z', Expected Format yyyyMMdd HHmmss.SSS: Unparseable date: "2023-07-28T14:20:51Z"

Database profile datetime data type Character

Cannot use yyyyMMdd HHmmss.SSS to send it to the DB!

declare @dt as datetime = ? -- '20230728 142051.525'
[22007][241] Conversion failed when converting date and/or time 
             from character string.

SQL Server requires ISO, in case characters are sent.

Last updated