Message Image  

Introducing date and time manipulation using IBM App Connect

 View Only
Fri July 03, 2020 04:41 AM

IBM App Connect lets you manipulate your data using expressions written in a data transformation and querying language called JSONata. JSONata includes some functions for date and time manipulation out of the box, and IBM App Connect adds more date and time functions of its own.

IBM App Connect and JSONata do not have specialized internal date or time objects. Generally, dates and times are represented in one of two ways:

  1. A number of milliseconds since midnight UTC on 1 January 1970 (see Unix time); for example, 946684800000 for midnight on 1 January 2000
  2. A formatted string e.g. “5/6/97”, “2018-05-11T13:16:34Z”, “Wed Jan 3 1987”

Date/time functions in IBM App Connect

  • $parseDate(str, formatStr[, timezone])
    Parses str using the supplied format string and returns a Unix time in milliseconds.
  • $formatDate(millis, formatStr[, timezone])
    Formats the Unix time in milliseconds as a string using the supplied format string.
  • $toMillis(str)
    Parses str as an ISO 8601-formatted date and returns a Unix time in milliseconds.
  • $fromMillis(millis)
    Formats the Unix time in milliseconds as a string using ISO 8601.
  • $millis()
    Gets the current Unix time in milliseconds.
  • $now()
    Gets the current instant as an ISO 8601-formatted string.

$parseDate(str, formatStr[, timezone])

Parses str using the supplied format string and returns a Unix time in milliseconds. For example: $parseDate("13/12/2017", "DD/MM/YYYY") returns the Unix time for 2017-12-13 00:00:00 UTC, i.e. 1513123200000.

str is assumed to be UTC, unless an explicit offset is part of the format. E.g. $parseDate('2013-06-01T00:00:00+04:00', 'YYYY-MM-DDTHH:mm:ssZ') indicates that str is from a location which is four hours ahead of UTC, so the returned value is the Unix time for 2013-05-31 20:00:00 UTC, i.e. 1370030400000.

formatStr uses Moment.js‘s formatting syntax internally.

In the absence of an explicit offset, you can specify a time zone for str using timezone. E.g. $parseDate('2000-01-01', 'YYYY-MM-DD', 'Europe/Berlin') returns the Unix time for 1999-11-31 23:00:00 UTC, i.e. 946681200000.

timezone is a time zone identifier from the tz database.

If both an explicit offset and timezone are specified, the explicit offset wins and timezone is ignored.

$formatDate(millis, formatStr[, timezone])

Formats the Unix time in milliseconds as a string using the supplied format string. For example: $formatDate(0, 'YYYY-MM-DD HH:mm:ss') returns '1970-01-01 00:00:00'.

formatStr uses Moment.js‘s formatting syntax internally.

If timezone is not specified, it is assumed that the output string should be UTC. If an explicit offset is part of the format string, that offset will be zero. E.g. $formatDate(0, 'HH:mm:ssZ') returns '00:00:00+00:00'.

Alternatively, you can specify an output time zone using timezone. E.g. $formatDate($toMillis('2013-01-01T00:00:00Z'), 'Europe/Berlin') returns '01:00:00', as this was the local time in Berlin at midnight UTC at that instant.

timezone is a time zone identifier from the tz database.

$toMillis(str)

Parses str as an ISO 8601-formatted date and returns a Unix time in milliseconds. For example: $toMillis('2017-02-13T01:50:00.000Z') returns 1486950600000.

This is equivalent to $parseDate(str, 'YYYY-MM-DDTHH:mm:ss.SSS[Z]').

$fromMillis(millis)

Formats the Unix time in milliseconds as a string using ISO 8601. For example: $fromMillis(1486950600000) returns '2017-02-13T01:50:00.000Z'.

This is equivalent to $formatDate(millis, 'YYYY-MM-DDTHH:mm:ss.SSS[Z]').

$millis()

Gets the current Unix time in milliseconds. For example: $millis() could return 1526035584073.

$now()

Gets the current instant as an ISO 8601-formatted string. For example: $now() could return '2018-05-11T13:35:14.967Z'.

This is equivalent to $fromMillis($millis()).

Example

A source application is returning timestamps in Pacific Time, using a US date format (month-day-year). For example, '3/25/2007 9:23pm'. We want to convert those timestamps to ISO 8601 format using UTC.

To parse the string to get a Unix millisecond count, we would use:

$parseDate($str, 'M/D/YYYY H:mma', 'America/Los_Angeles')

To write the Unix millisecond count out using ISO 8601, we would use any of these (they’re equivalent):

$formatDate($millis, 'YYYY-MM-DDTHH:mm:ss.SSS[Z]', 'Etc/UTC')
$formatDate($millis, 'YYYY-MM-DDTHH:mm:ss.SSS[Z]')
$fromMillis($millis)

Putting it all together:

$formatDate($parseDate($str, 'M/D/YYYY H:mma', 'America/Los_Angeles'), 'YYYY-MM-DDTHH:mm:ss.SSS[Z]')

For example, $formatDate($parseDate('3/25/2007 9:23pm', 'M/D/YYYY H:mma', 'America/Los_Angeles'), 'YYYY-MM-DDTHH:mm:ss.SSS[Z]') returns '2007-03-26T04:23:00.000Z'.


#AppConnect
#date-and-time
#JSONata