Version

    Date manipulation steps

    Current date and time

    The Current date and time step writes current date and time into designated column. Same value will be written into each row in dataset.

    Parameters

    • Target column: required, configure the column which will receive the current date and time. Output will always be of date type.

      • Overwrite existing column: overwrite the input column with the result.

      • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed at the very end of the dataset.

    Examples
    Output value Description

    2023-03-06 12:34:16.285

    Current date and time
    Remarks

    • The step will return the same value for each row in the data set regardless of how long the job execution takes. The value written is the date and time when the Wrangler job was started.

    • The value returned is with millisecond precision.

    See also
    Date add/subtract

    The Date add/subtract step adds or subtracts a specified number of time units (days, weeks, months, years, hours, minutes, seconds, milliseconds).

    Parameters

    • Input column: required, a date column to add specified number of time units to.

    • Number of units: required, the number of units to be added or subtracted from the selected date. Use negative values to subtract from the date.

    • Unit: required, the time unit to be added/subtracted. Multiple options are allowed:

      • days

      • weeks

      • months

      • years

      • hours

      • minutes

      • seconds

      • milliseconds

    • Target column: required, configure the column which will receive the output.

      • Write result to the current column: outputs data into the Input column.

      • Create new column with name: create a new column with the specified name. Name of the new column can contain spaces or special characters - the technical column name will be created automatically. The new column will be placed right next to the Input column.

    Examples

    Sample date: 2023-01-09 03:11:00

    Input date Unit Number of units Result Note

    2023-01-09 03:11:00

    Days

    8

    2023-01-17 03:11:00

    2023-01-09 03:11:00

    Days

    -8

    2023-01-01 03:11:00

    2023-01-09 03:11:00

    Months

    2

    2023-03-09 03:11:00

    2023-01-09 03:11:00

    Days

    60

    2023-03-10 03:11:00

    Note the difference between adding 2 months vs. 60 days - the months add calendar months so add different number of days depending on the input date.

    2023-01-09 03:11:00

    Minutes

    123456

    2023-08-02 21:02:00

    Any number works as number of units - there is no need to limit the values to small numbers.

    No value

    Any

    Any

    Error

    Trying to add/subtract to/from empty values results in an error.

    Error

    Any

    Any

    Error

    Applying the step to an Error value results in an Error.
    See also
    Date difference

    The Date difference step calculates the time difference in the specified time unit (days, weeks, months, years, hours, minutes, seconds, or milliseconds) between two date columns.

    Parameters

    • First date column: required, select a date column from the column list.

    • Second date column: required, select a date column from the column list.

    • Unit: required, select the date unit that will be used in the calculation (days, weeks, months, years, hours, minutes, seconds, milliseconds).

    • Target column: required, configure the column which will receive the output. Output will always be an integer.

      • Write result to the current column: outputs data into the First date column.

      • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed next to the First date column.

    Examples
    First date column Second date column Unit Result Notes

    2023-03-31

    2023-01-01

    Days

    89

    2023-01-01

    2023-03-31

    Days

    -89

    Second date is after the first, result is negative.

    2023-03-31

    2023-01-01

    Months

    2

    2023-01-01

    2023-03-31

    Months

    -2

    Second date is after the first, result is negative.

    2023-03-25 04:51:25

    2023-03-25 03:00:00

    Hours

    1

    Difference is 1 hour 51 minutes 25 seconds which is truncated to 1 hour.

    2023-03-25 04:51:25

    2023-03-25 03:00:00

    Minutes

    111

    Difference is 1 hour 51 minutes 25 seconds which is 111 minutes and 25 seconds which is truncated to 111 minutes.

    2023-03-25 04:51:25

    2023-03-25 03:00:00

    Seconds

    6685

    Difference is 6685 seconds exactly, no truncation is necessary.

    No value

    Any

    Any

    Error

    Error
    Remarks

    • The order of the dates in the step parameters matters. The step always calculates First date - Second date, which might result in negative values if Second date is after the First date.

    • The result is truncated to the whole number of units. For example, if the time difference between the values is 2 hours 45 minutes, the result will be "2" if the time unit is hours.

    • If any of the input dates is No value, the step will fail.

    • If any of the input dates is an Error, the step will fail.

    See also
    Get part of date

    The Get part of date step extracts the specified time unit (day of week, day of month, month, year, hours, minutes, seconds, milliseconds) from a date column.

    Parameters

    • Input column: required, select a date column from the column list.

    • Part: required, the time unit to be extracted from input date. Following options are available:

      • day of week

      • day of month

      • month

      • year

      • hours

      • minutes

      • seconds

      • milliseconds

    • Target column: required, configure the column which will receive the output. Output will always be an integer.

      • Write result to the current column: outputs data into the Input column.

      • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed next to the Input column.

    Examples

    Sample date: 2023-05-09 03:11:00

    Date unit Result

    Day of week

    2

    Day of month

    9

    Month

    5

    Minute

    11
    Returned values

    The values returned by the step depend on the selected unit:

    Unit Minimum value Maximum value Note

    day of week

    1

    7 (Sunday)

    Value of 1 always means Monday and 7 is Sunday regardless of the start day of the week.

    day of month

    1

    31

    month

    1

    12

    year

    292278994 (292 million)

    -292278994 (-292 million)

    Dates can store exact date and time millions of years in the past or in the future.

    hours

    0

    23

    24-hour format is used regardless of the display format of the input date column (i.e. 4pm will return 16).

    minutes

    0

    59

    seconds

    0

    59

    milliseconds

    0

    999
    See also