Why is date in string automatically transformed to different date/time format?

[Michal]

Hi,

I have a simple task step which parses JSON using JS mapper. As a task step input I send JSON which has been received from API call. The JSON contains property updated which is loaded within the step and returned without any further transformation. To my surprise, step returns date/time within different format than it is. Is there any further transformation within JS mapper connecter when the json is returned? Thank you

[tomas (Tomas Belak)]

Michal,

can you please paste the whole script and input data as code into the topic?

Tomas.

[Michal]

Sure thing! Here is the debug script:

// creating input data
const _inputString = `[
  {
    "JSON": [
  {
    "defaultLanguage": "cs",
    "devices": [
      {
        "controller": "test-pool-1",
        "companyId": 10,
        "address": {
          "address": "nersfiu, 616 00 Brno, Česko",
          "code": "cz",
          "approximateLng": "16.56829990640513",
          "lng": "16.5672",
          "city": null,
          "postalCode": "616 00",
          "placeId": "EixTb2Nob3JvdmEsIDYxNiAwMCBCcm5vLcW9YWJvdsWZZXNreSwgQ3plY2hpYSIuKiwKFAoSCTXQMQyglhJHEePpPaVjVjQcEhQKEglnlDfEHpQSRxHgzhRmD68ABQ",
          "approximateLat": "49.216644597728575",
          "id": 32,
          "deviceId": 5,
          "lat": "49.2134"
        },
        "customName": "Klaus Flicke",
        "id": 5,
        "updated": "2022-08-29T13:58:45.336Z"
      }
    ],
    "contactEmailAddresses": null,
    "name": "MNB",
    "emailsForEventNotifications": null,
    "id": 10,
    "updated": "2020-09-09T09:18:04.06Z",
    "users": [
      {
        "firstName": "",
        "lastName": "",
        "phoneNumber": null,
        "id": 60,
        "updated": "2020-09-10T11:19:40.362Z",
        "email": "petr.skraivan@mnb.cz"
      },
      {
        "firstName": "",
        "lastName": "",
        "phoneNumber": null,
        "id": 62,
        "updated": "2020-09-10T11:20:02.51Z",
        "email": "martin.risapa@mnb.cz"
      },
      {
        "firstName": "",
        "lastName": "",
        "phoneNumber": null,
        "id": 63,
        "updated": "2020-09-11T07:06:15.031Z",
        "email": "antonin.snajcdr@mnb.cz"
      },
      {
        "firstName": "",
        "lastName": "",
        "phoneNumber": null,
        "id": 674,
        "updated": "2020-09-21T11:16:00.537Z",
        "email": "Jan.Kopvcil@mnb.cz"
      },
      {
        "firstName": "",
        "lastName": "",
        "phoneNumber": null,
        "id": 712,
        "updated": "2020-09-23T06:10:42.96Z",
        "email": "bohdan.blafha@mnb.cz"
      },
      {
        "firstName": "",
        "lastName": "",
        "phoneNumber": null,
        "id": 770,
        "updated": "2020-09-24T14:48:00.24Z",
        "email": "Pavel.Spsulak@mnb.cz"
      },
      {
        "firstName": "Ondrej",
        "lastName": "Sebefdssta",
        "phoneNumber": null,
        "id": 11,
        "updated": "2020-04-24T08:07:34.475Z",
        "email": "ondrej.sebvcesta@sdf.com"
      }
    ]
  }
]
  }
]`
const inputData = JSON.parse(_inputString);

// creating log methods
const log = {
  trace: (str) => console.trace(str),
  debug: (str) => console.debug(str),
  info: (str) => console.info(str),
  warn: (str) => console.warn(str),
  error: function (err) {
    throw err;
  }
}

// creating variables
var outputData;
var _outputString;
const TaskRunID = 1;
const EndpointTokenName = null;
const EndpointTokenID = null;
const DataCheckpoint = null;

// execute method with defined statement
function execute() {
  var output = [];
  var integrationWith = "Enendio-";

  inputData.forEach(row => {
    rowData = row.JSON;

    rowData.forEach(company => {
      //log.info(company.id);
      //--------------------------------
      //  parse company data
      //--------------------------------
      var companyID = integrationWith + company.id;
      var companyTimestamp = company.updated;
      var EmailAddress = "";

      if (company.contactEmailAddresses != null) {
        company.contactEmailAddresses.forEach(email => {
          EmailAddress = email.EmailAddress;
        });
      }

      output.push({
        "SectionID": 1043,
        "RequestorLogin": null,
        "NextUserLogin": null,
        "Data":
          company.id + "|" +
          company.name + "|" +
          company.defaultLanguage + "|" +
          EmailAddress + "|" +
          EmailAddress + "|" +
          companyTimestamp + "|" +
          companyID,
        "IntegrationID": companyID,
        "LastUpdate": companyTimestamp
      });

      //--------------------------------
      //  parse device data
      //--------------------------------
      company.devices.forEach(device => {
        var deviceID = integrationWith + device.id;
        var address = device.address;
        var deviceTimestamp = device.updated;

        output.push({
          "SectionID": 1045,
          "RequestorLogin": null,
          "NextUserLogin": null,
          "Data":
            deviceID + "|" +
            device.controller + "|" +
            "|" +   // customer ID does not exist in import data
            company.name + "|" +
            device.customName + "|" +  // contact name does not exist in import data
            "|" +  // contact phone does not exist in import data
            "|" +  // contact mail does not exist in import data
            "|" +  // contact note does not exist in import data
            (address.address == null ? "" : address.address) + "|" +
            (address.city == null ? "" : address.city) + "|" +
            (address.postalCode == null ? "" : address.postalCode) + "|" +
            (address.code == null ? "" : address.code) + "|" +
            deviceTimestamp + "|" +
            address.lat + "|" +
            address.lng + "|" +
            address.approximateLat + "|" +
            address.approximateLng + "|" +
            companyID + "|" +
            device.id,
          "IntegrationID": deviceID,
          "LastUpdate": deviceTimestamp
        });
      });

      //--------------------------------
      //  parse user data
      //--------------------------------
      company.users.forEach(user => {
        var userID = integrationWith + user.id;
        var userTimestamp = user.updated);

        output.push({
          "SectionID": 1046,
          "RequestorLogin": null,
          "NextUserLogin": null,
          "Data":
            user.id + "|" +
            user.firstName + "|" +
            user.lastName + "|" +
            user.email + "|" +
            (user.phoneNumber == null ? "" : user.phoneNumber) + "|" +
            userTimestamp + "|" +
            userID + "|" +
            companyID,
          "IntegrationID": userID,
          "LastUpdate": userTimestamp
        });
      });
    });
  });

  //log.info(JSON.stringify(output))

  return output;
};

// show returned data in console
console.log(execute());

[libor.zoubek]

Michal, can you screenshot your output schema? IMHO LastUpdate column is not type of Date.

Let me try to describe what happens under the hood

• In order to prepare inputData your input data is always processed by JSON.parse javascript function, which means that it converts strings which match datetime into javascript DateTime objects
• Value returned from JS Mapper statement is always some array of Javascript objects (let’s say rows), which is then internally transformed into internal types which must match your output schema.
• For each returned row, we try to map it’s properties into selected output schema. When we hit LastUpdate which is unexpectedly Date we just convert it to String type. Such default conversion unfortunately does not use DateTime ISO format.

Solutions for you

• Make sure returned values for your JS statement match output schema
• In case you for some reason need LastUpdate column as String you need to manually convert using .toISOString()