Dates
Last updated
Last updated
The below-mentioned transforms are available under the Dates category:
The transform adds two-time values. It can either add the selected column with a time value or time from another column.
Please Note: The transform supports adding time into ‘hh:mm:ss.mmm’ and ‘hh:mm:ss’ formats.
Use with: Specify whether to fill with a value or another column value
Column/ Value: The value with which the column must be added, or the column with which the selected column value must be added.
The Add Duration transform is applied to the timecol2, and the selected other column is timecol3 to configure the transform:
Select a column with the time values (In this case, the selected column is Time Out).
Open the Transforms tab.
Select the Add Duration transform from the Date transform category.
Enable the Create new column option, if you wish to display the transformed data in a new column.
Select the Other Column option from the drop-down option.
Select another column using the drop-down option from where the duration values are to be counted (In this case, the selected column is Time In column).
Click the Submit option.
A new column gets created displaying the duration values as displayed below:
The Add Duration transform has been applied to the Time Out column with Value option where in the set value is 2 hours.
A new column gets added to the Data Grid displaying duration based on the set value as shown in the below-given image:
It adds the time duration specified to the selected datetime column.
Select a column with Date values from the data grid.
Open the Transforms tab.
Select the Add Interval to Date transform from the Dates category.
Enable the Create new column option to create a new column with the transform result.
Input Format: It is used to specify the format of the selected Date column format. It can have values ‘Year first’, ‘Month first’, and ‘Day first.’
Value Type: It specifies the type of duration which acts as the operand for the addition. The value type can be years, months, days, weeks, hours, minutes or milliseconds
Value: The value or the operand that must be added with the selected column
Click the Submit option.
E.g., The Add Interval to Date transform has been applied to the DELIVERY_DATE with the selected value of 2 months,
As a result, a new column gets added to the Data Grid reflecting the transformed data as per the set value.
Please Note: The transform supports the datetime column of ‘yyyy-mm-dd’ into the ‘hh:mm:ss’ format.
Converts the DateTime value to the corresponding value of the specified time zone. Input can be a column of Datetime values and it’s assumed to be in the UTC time zone.
Please Note: Inputs with time zone offsets are invalid.
Check out the walk-around on the Convert From UTC transform.
Steps to perform the transformation:
Select a DateTime column from the Dataset.
Navigate to the Transforms tab.
Select the Covert From UTC transform from the Dates category
Pass new column name.
Pass the Target Time zone in which the date to be converted.
Click the Submit option.
Result will come in a new column with the converted time zone present in it.
The Convert Timezone transformation converts Datetime value in a specified time zone to corresponding value second specified time zone.
Please Note: Inputs with time zone offsets are invalid.
Steps to perform the transformation:
Select a DateTime column from the Data Grid.
Navigate to the Dates transforms category.
Select the Convert Timezone transform.
Pass new column name.
Pass the Source Time zone for the selected date column.
Pass the Target Time zone in which the date to be converted.
Click the Submit option.
Result will come in a new column with the converted time zone.
The Convert To UTC transform converts the DateTime value in a specified time zone to the corresponding value in the UTC time zone. Input can be a column of DateTime values.
Check out the given walk-through on how to Convert to UTC.
Please Note: Inputs with time zone offsets are invalid.
Steps to perform the transformation:
Select a DateTime column from the Data Grid.
Open the Transforms tab.
Navigate to the Dates transforms category.
Select the Convert To UTC transform.
Pass new column name.
Pass Source Time zone from which the date is to be converted to UTC.
Click the Submit option.
Result will come in a new column containing the converted UTC format.
It extracts the date part from a selected column with a date value. The date parts that can be extracted include day, month, year, the day of the week, the day of the year and the week of the year.
Select a Datetime column from the Data Grid.
Open the Transforms tab.
Select the Extract Date Part transform from the Dates transforms category.
Select an Input Format based on the selected column.
Day: It extracts day from a date
Month: It extracts the month from a date/datetime. We can specify the pattern in which the month value has to be returned. Month pattern can be 0-12, Jan - Dec or January - December
Year: It extracts the year from a date. We can specify the pattern in which the year has to be returned. The year pattern can be in the ‘yy’ or ‘yyyy’ format.
Day of Week: It returns the day of the week for the selected date. Day of week pattern can also be specified. The pattern can be 1-7, Sun-Sat or Sunday-Saturday
Day of Year: It returns a number between 1 and 365, which indicates the sequential day number starting with day one on January 1st.
Week of Year: It replaces a number between 1 and 53, which indicates the sequential week number beginning with 1 for the week January 1st falls.
Quarter: It displays the date value based on the quarter, select a quarter pattern using the drop-down option. The supported options are Normal Quarter, Financial Quarter, and Custom Quarter (the user can define the month value for the custom quarter option).
Click the Submit option after selecting all the Date Parts that you wish to extract from the targeted column.
E.g., This transform is applied on the Expected Joining Date column by selecting the Day of Week as a part to be extracted from the selected column values.
As a result, it creates a new column displaying a number from 1-7 indicating a day of the week for the concerned date.
Please Note: The transform supports Date and DateTimes format (date hh:mm:ss).
Extract the time units from a selected column with a time value. The time units that get extracted include hours, minutes, seconds, milliseconds, and time to milliseconds.
Select a Datetime column from the Data Grid.
Open the Transforms tab.
Select the Extract Time Unit transform from the Dates transforms category.
Hours: Extracts hours from a time
Minutes: Extracts minutes from a time
Seconds: Extracts seconds from a time
Milliseconds: Extracts milliseconds from a time
Time to Milliseconds: Converts the time given to milliseconds
Click the Submit option.
The Extract Time Unit transform is applied to the DELIVERY_DATE column selecting all the available format types:
As a result, the time gets extracted in the set time units and no. of columns get added based on the selected time unit options. E.g., In this case, 5 new columns get added to the Data Grid displaying the extracted time values.
Please Note: The transform supports time format like- hh:mm:ss:mmm, hh:mm:ss, hh:mm
The transform finds the difference between two date values. It can either subtract the selected column with a date value or date from another column. The transformed value can replace the existing column value or can be added as a new column.
Select a Date column from the Data Grid.
Open the Transforms tab.
Select the Find Date Difference transform from the Dates transforms category.
Enable the Create new column option to create a new column with the transformed data.
Input Format: Specifies the format of the given date column.
Use with: Specify whether to fill with a value or another column value.
Value Hint: Specify format of value from which you want to find the difference.
Value: Pass the date value from where you want to find the date difference.
Click the Submit option.
In this case, the Find Date Duration transform has been used with the value 2015-03-09.
As a result, a new column gets created with the set Date Duration value.
The users can change the format of a date column by using this transform.
Select a Date column from the Data Grid.
Open the Transforms tab.
Select the Format Date transform from the Dates transforms category.
Source Format Hint: Specifies the current format of the date column.
Target Format: Specifies what we want first (Year, Month, Day) in our output format of the date column.
Year Pattern: Specifies the format of the year (yyyy or yy) in the output date column.
Month Pattern: It specifies the format of the month (number, Jan-Dec, January-December) in the output date column.
Delimiter: Specifies Delimiter (like- slash, a hyphen, comma, full stop, space) for the output date column.
Include Timestamp: It adds a timestamp to the current date format if enabled with a tick mark.
Click the Submit option.
As a result, it displays the values of the selected column as per the set transform format of Date:
The From Unix Time transform converts the Unix time into a specified format. The From Unix Time transform has been applied on the Date column that contains the values in the Unix format,
Select a Date column from the Data Grid.
Open the Transforms tab.
Select the From Unix Time transform from the Dates transforms category.
Enable the Create new column option to create a new column with the transformed data values.
Provide the Output Format in which you want to get the result data.
Click the Submit option.
As a result, a new column gets added to the Data Grid in the set Date format with the converted Unix values:
The Sub Interval to Date transform subtracts specified value(interval) from the given date column. The transformed value can replace the existing column value or can be added as a new column.
Select a Date column from the Data Grid.
Open the Transforms tab.
Select the Sub Interval to Date transform from the Dates transforms category.
Input Format: Format of date column(given) should be specified here.
Value Type: It specifies what we want to subtract like years, months, days, weeks, etc.
Value: It specifies how many years/months/days (value type) we want to subtract.
Click the Submit option.
E.g., The Sub Interval to Date transform is applied on the Dates column with the 2 months as value to get Sub Interval to the given date.
As a result, a new column gets created with the set sub interval values:
The transform ‘Subtract Duration’ deducts the time values in two ways. It can either subtract the selected column with a time value or time from another column. The transform supports subtracting time into ‘hh:mm:ss.mmm’,‘ hh:mm:ss’, and 'hh:mm’ formats. The transformed value can replace the existing column value or can be added as a new column.
Select a column with the time values from the dataset.
Navigate to the Transforms tab.
Select the Subtract Duration transform from the Dates category.
Enable the Create new column option, if you wish to create a new column with the result data.
Use with: Specify whether to fill with a value or another column value
Column/ Value: The value with which the column must be subtracted, or the column with which the selected column value must be subtracted.
Click the Submit option.
The result will get displayed based on your Use with selection as described below:
The Subtract Duration transform has been applied to the Time Out column, the selected other column is Time In,
As a result, a new column gets added to the Data Grid with the subtracted duration out of the selected columns:
The Subtract Duration transform has been applied to the Time Out_Time In_sub_duration_1, the selected value is 01:03:02,
As a result, a new column gets added to the Data Grid with the remaining values after subtracting the set values from the targeted column:
