Overview
The date math transform allows you to add, subtract and round components of a timestamp to or from an incoming value. It also allows you to work with a referential value of "now" to run operations against the current date and time instead of a fixed value.
The output format for the DateMath transform is "yyyy-MM-dd'T'HH:mm". When using this transform inside another transform (e.g. dateCompare), make sure to convert to ISO8601 first.
Other Considerations
-
The input datetime value must always be in ISO8601 format, in UTC time zone:
- yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
- 2020-10-28T12:00:00.000Z, as an example
- The dateFormat transform may be used to assist in getting data into this format
-
The industry standard for rounding is actually date/time truncation. When rounding down, the fractional value is truncated from the incoming data. When rounding up, the fractional value is truncated and the next unit of time is added. Please refer to the Transform Structure section below for examples.
-
When working with rounding, the "week" unit of time is not supported as a metric, and attempting to round up or down a week will result in an error.
-
When using the “now” keyword, if an input date is also supplied via the input parameter, whether implicitly or explicitly defined, the transform will prefer using “now” and ignore data in theinputattribute.
Transform Structure
The date math transform takes whatever value is provided as the input and executes addition, subtraction and/or rounding operations to that value based off an expression configuration value. As indicated above, the input datetime must always be in ISO8601 format. The expression value leverages the following abbreviations to indicate what date or time component to evaluate:
- "y" - year
- "M" - month
- "w" - week
- "d" - day
- "h" - hour
- "m" - minute
- "s" - second
- "now" - the current instant in time
Also, the operational logic is defined by usage of one of the following symbols:
- "+" - add; this must be followed by a valid time unit
- "-" - subtract; this must be followed by a valid time unit
- "/" - round; this must be followed by a valid time unit
Some examples of expressions are:
"expression": "now" returns the current date and time"expression": "now/h" returns the current date and time, rounded to the hour"expression": "now+1w" returns one week from the current date and time"expression": "now+1y+1M+2d-4h+1m-3s/s" returns the current date and time plus one year, one month, two days, minus four hours, plus one minute and minus three seconds, rounded to the second"expression": "+3M" returns the date and time that would be three months more than the value provided as an input to the transform
Example
{
"attributes": {
"expression": "+3M/h",
"roundUp": true,
"input": {
"attributes": {
"sourceName": "HR Source",
"attributeName": "startDate"
},
"type": "accountAttribute"
}
},
"type": "dateMath",
"name": "Test Date Math Transform"
}
Attributes
-
Required Attributes
- type - This must always be set to
dateMath
- name - This is a required attribute for all transforms, and represents the name of the transform as it will appear in the UI's dropdowns
- expression - A string value of the date and time components to operation on, along with the math operations to execute. As described in the Transform Structure examples above, multiple operations on multiple components are supported.
-
Optional Attributes
- requiresPeriodicRefresh - A
true or false value that indicates whether the transform logic should be re-evaluated every evening as part of the identity refresh process.
-
roundUp - A boolean value (true/false) to indicate whether the transform should round up or down when a rounding ("/") operation is defined in the expression. If not provided, the transform will default to false
true indicates the transform should round up (i.e., truncate the fractional date/time component indicated and then add one unit of that component)false indicates the transform should round down (i.e., truncate the fractional date/time component indicated)
-
input - This is an optional attribute that can explicitly define the input data which will be fed into the transform logic. If input is not provided, the transform will take its input from the source and attribute combination configured via the UI.
Examples
Example 1
{
"attributes": {
"expression": "now-5d/d",
"roundUp": false
},
"type": "dateMath",
"name": "Test Date Math Transform"
}
This transform will take the current date, subtract five days from it, and round down to the lowest day.
Example 2
{
"attributes": {
"expression": "+12h/s",
"roundUp": true,
"input": {
"attributes": {
"input": {
"attributes": {
"sourceName": "HR Source",
"attributeName": "startDate"
},
"type": "accountAttribute"
},
"inputFormat": "MMM dd yyyy, HH:mm:ss.SSS",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
}
},
"type": "dateMath",
"name": "Test Date Math Transform"
}
This transform will take the startDate attribute from a user's record in the "HR Source," convert it from its native format to an ISO8601-formatted string, then add twelve hours to it. The final value will then be rounded up to the next second.
Example 3
{
"type": "dateFormat",
"name": "WD - HireDate",
"attributes": {
"input": {
"attributes": {
"expression": "+1d",
"input": {
"attributes": {
"input": {
"attributes": {
"attributeName": "HIREDATE",
"sourceName": "Workday"
},
"type": "accountAttribute"
},
"inputFormat": "MM/dd/yyyy",
"outputFormat": "ISO8601"
},
"type": "dateFormat"
},
"roundUp": true
},
"type": "dateMath"
},
"inputFormat": "yyyy-MM-dd'T'HH:mm",
"outputFormat": "EPOCH_TIME_WIN32"
}
}
This transform will take the HIREDATE from Workday, convert it to ISO8601 to be used in the Date Math transform. The Date Math transform will then create a new Date of HIREDATE + 1. Since that is then outputted in the format "yyyy-MM-dd'T'HH:mm", this can then be used in a dateFormat transform to give a WIN32 formatted date.
References
