Date manipulation steps
-
Current date and time: return current date and time
-
Date add/subtract: add or subtract time units
-
Date difference: calculate time difference between two date columns
-
Get part of date: extract time unit from dates
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 existing 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.
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 date: required, choose whether to use today’s date or dates from a column.
-
Today: add or subtract time units from today’s date. This value changes daily.
-
Columns group columns: Pick an existing date column from the list.
-
-
Number of units: required, the number of units to be added or subtracted from the selected date. Use negative values to subtract.
-
Constant value: enter a constant value.
-
Select from column: choose a column with integer values. The value from each row will be applied to today’s date or the corresponding row in the input column.
-
-
Unit: required, the time unit to be added/subtracted. Pick one of the following:
-
days
-
weeks
-
months
-
years
-
hours
-
minutes
-
seconds
-
milliseconds
-
-
Target column: required, configure the column which will receive the output.
-
Overwrite existing column: output data into an existing 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 selected Input date column, if a column is selected. If Today is selected as the input date, the column will be added at the end of the table.
-
Examples
Sample date: 2023-01-09 03:11:00
| Input date | Unit | Number of units | Result | Note |
|---|---|---|---|---|
|
Days |
8 |
2023-01-17 03:11:00 |
|
|
Days |
-8 |
2023-01-01 03:11:00 |
|
|
Months |
2 |
2023-03-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. |
|
Minutes |
123456 |
|
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. |
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: required, choose whether to use today’s date or dates from a column.
-
Today: add or subtract time units from today’s date. This value changes daily.
-
Columns group columns: Pick an existing date column from the list.
-
-
Second date: required, select a date column from the column list.
-
Today: add or subtract time units from today’s date. This value changes daily.
-
Columns group columns: Pick an existing date column from the 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.
-
Overwrite existing column: output data into an existing 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 added at the end of the table.
-
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 ifSecond dateis after theFirst 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.
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 date: required, choose whether to use today’s date or dates from a column.
-
Today: Use today’s date as the calculation basis. This value changes daily.
-
Columns group columns: Pick an existing date column from the list.
-
-
Part: required, the time unit to be extracted from the input date. The 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.
-
Overwrite existing column: overwrite existing 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 right next to the selected Input date column, if a column is selected. If Today is selected as the input date, the column will be added at the end of the table.
-
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 |