# Dates

## **Add Duration** <a href="#add-duration" id="add-duration"></a>

The transform adds two-time values. It can either add the selected column with a time value or time from another column.&#x20;

{% hint style="info" %}
_<mark style="color:green;">Please Note:</mark> The transform supports adding time into ‘hh:mm:ss.mmm’ and ‘hh:mm:ss’ formats._
{% endhint %}

* _**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.

### Transform Configuration with Other Column  <a href="#transform-configuration-with-other-column" id="transform-configuration-with-other-column"></a>

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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/fLo7juvqxcu6HgbLDdg4/image.png" alt=""><figcaption><p><em><strong>Applying the Add Duration Transform</strong></em></p></figcaption></figure>

* A new column gets created displaying the duration values as displayed below:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/fbYvjU1ySWC4NhHTNIxJ/image.png" alt=""><figcaption><p><em><strong>Result column with the Oher Column option</strong></em></p></figcaption></figure>

### Transform Configuration with Value <a href="#transform-configuration-with-value" id="transform-configuration-with-value"></a>

The _**Add Duration**_ transform has been applied to the _**Time Out**_ column with Value option where in the set value is **2 hours**.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/CFVAZTQIFoeVRUHz9eb8/image.png" alt=""><figcaption></figcaption></figure>

A new column gets added to the Data Grid displaying duration based on the set value as shown in the below-given image:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/MaFDyCiz8OHLIW7pN6dO/image.png" alt=""><figcaption></figcaption></figure>

## **Add Interval to Date** <a href="#add-interval-to-date" id="add-interval-to-date"></a>

It adds the time duration specified to the selected datetime column.

{% hint style="success" %}
_Check out the given walk-through on how to use the Add Interval to Date transform._
{% endhint %}

{% embed url="https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FeLjFuOeMrSTmcTW4zRdr%2FDates_AddIntervaltoDate.mp4?alt=media&token=7965506d-b362-45c7-8343-1907407c0dc8" %}
_**Add Interval to Date**_
{% endembed %}

* 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 Date column with the selected value of 3 months,

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/TVz1HPJsgMGQTv4FYChP/image.png" alt=""><figcaption></figcaption></figure>

As a result, a new column gets added to the Data Grid reflecting the transformed data as per the set value.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/fjYn0AS22XDr8GVNBWpW/image.png" alt=""><figcaption><p><em><strong>The result column for the Add Interval to Date Transform</strong></em></p></figcaption></figure>

{% hint style="info" %}
_<mark style="color:green;">Please Note:</mark>_ _The transform supports the datetime column of ‘yyyy-mm-dd’ into the ‘hh:mm:ss’ format._
{% endhint %}

## **Convert From UTC** <a href="#extract-date-part" id="extract-date-part"></a>

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.

{% hint style="info" %}
_<mark style="color:green;">Please Note:</mark> Inputs with time zone offsets are invalid._
{% endhint %}

{% hint style="success" %}
_Check out the walk-around on the Convert From UTC transform._
{% endhint %}

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/Np6zAJfsF97Gd8pakl7M/ConvertFromUTC.gif" alt=""><figcaption></figcaption></figure>

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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/WXQggKO1B3jz9KCdZ0tF/image.png" alt=""><figcaption></figcaption></figure>

* Result will come in a new column with the converted time zone present in it.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/hnRW5JpRHBL1YvvQfFg2/image.png" alt=""><figcaption></figcaption></figure>

## Convert Timezone

The Convert Timezone transformation converts Datetime value in a specified time zone to corresponding value second specified time zone.

{% hint style="info" %}
_<mark style="color:green;">Please Note:</mark> Inputs with time zone offsets are invalid._
{% endhint %}

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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/Vy2Dg6bCgsRZsIjQ8sr7/image.png" alt=""><figcaption></figcaption></figure>

* Result will come in a new column with the converted time zone.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/oEbwVKIdA7ytFgpL61Ze/image.png" alt=""><figcaption></figcaption></figure>

## Convert To UTC

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.

{% hint style="info" %}
_Check out the given walk-through on how to Convert to UTC._
{% endhint %}

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/U9Y2kTWkqKT1gcvZwNwy/ConvertToUTC.gif" alt=""><figcaption><p><em><strong>Convert to UTC</strong></em></p></figcaption></figure>

{% hint style="info" %}
_<mark style="color:green;">Please Note:</mark> Inputs with time zone offsets are invalid._&#x20;
{% endhint %}

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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/8zpxF2wv36kd8x3gxmVW/image.png" alt=""><figcaption></figcaption></figure>

* Result will come in a new column containing the converted UTC format.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/xztqQMCsoqA0UBwlOCE0/image.png" alt=""><figcaption></figcaption></figure>

## **Extract Date Part** <a href="#extract-date-part" id="extract-date-part"></a>

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.

{% hint style="success" %}
_Check out the given illustration on how to use the Extract Date Part transform._
{% endhint %}

{% embed url="https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FkoWDCbHESFatm6WQuUVh%2FDates_ExtractDatePart.mp4?alt=media&token=2dba484e-a651-4292-9faa-1d752dee753e" %}
_**Extract Date Part**_
{% endembed %}

* 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).

&#x20;            ![](https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/KxTrS98RznssOOjyTNwu/image.png)



* 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 _**Date**_ column by selecting the _**Day, Month (Jan-Dec format), Year (in yy pattern), Day of Week**_ (Sunday- Saturday), _**Day of year**_, _**Quarter**_ as a part to be extracted from the selected column values.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/JuM7RY1SGqqff1F2jltM/image.png" alt=""><figcaption></figcaption></figure>

As a result, it creates a new columns displaying all the selected transform values.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/9HNkw2axefUo8OO9BoVQ/image.png" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
_<mark style="color:green;">Please Note:</mark> The transform supports Date and DateTimes format (date hh:mm:ss)._
{% endhint %}

## **Extract Time Unit** <a href="#extract-time" id="extract-time"></a>

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:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/OPN9ezwt6Ytsjw4Mfj39/image.png" alt=""><figcaption></figcaption></figure>

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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/xmvjHXxxKalJL8Ww3Lqt/image.png" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}
_<mark style="color:green;">Please Note</mark>: The transform supports time format like- hh:mm:ss:mmm, hh:mm:ss, hh:mm_
{% endhint %}

## **Find Date Difference** <a href="#find-date-difference" id="find-date-difference"></a>

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 wit**h: 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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/4PIiQ4HeRPVUihE0Rgeh/image.png" alt=""><figcaption></figcaption></figure>

* As a result, a new column gets created with the set Date Duration value.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/49hKPCGyI7Z4FJBr9MPV/image.png" alt=""><figcaption></figcaption></figure>

## **Format Date** <a href="#format-date" id="format-date"></a>

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.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/Gbv6SgkjxobTNOGDrg1N/image.png" alt=""><figcaption></figcaption></figure>

As a result, it displays the values of the selected column as per the set transform format of Date:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/eycfhfhZGJVPJUMMvkRI/image.png" alt=""><figcaption></figcaption></figure>

## **From Unix Time** <a href="#from-unix-time" id="from-unix-time"></a>

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.

{% hint style="success" %}
_Check out the given illustration on how to user From Unix Time transform._
{% endhint %}

{% embed url="https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FislIDS8s83ZkA2cxSA2z%2FDates_FromUnixTime.mp4?alt=media&token=f364b94b-6bd9-4807-ad9e-844ee9ea72b5" %}

* Select a _**Date**_ column from the Data Grid that contains data in the Unix time.
* 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.&#x20;

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/OwVZVrjSC2xpVikYxdgl/image.png" alt=""><figcaption></figcaption></figure>

* As a result, a new column gets added to the _**Data Grid**_ in the set Date format with the converted Unix values:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/GXne9HhAXH0gXuGefgvq/image.png" alt=""><figcaption></figcaption></figure>

## **Sub Interval to Date** <a href="#sub-interval-to-date" id="sub-interval-to-date"></a>

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.

{% hint style="success" %}
_Check out the given illustration on how to use Sub Interval to Date._
{% endhint %}

{% embed url="https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIctE5LjGWDD6zEdW4vpJ%2Fuploads%2FvpTBeqOAe9VuZXWSODCp%2FDates_SubIntervaltoDate.mp4?alt=media&token=a8f46c21-1a5f-44de-915f-973a9111b0cb" %}
_**Applying Sub Interval to Date Transform**_
{% endembed %}

* Select a _**Date**_ column from the Data Grid.
* Open the _**Transforms**_ tab.
* Select the _**Sub Interval to Date**_ transform from the _**Dates**_ transforms category.
* Provide the following information to apply the transform:
  * Enable the _**Crate new column**_ option to create a new column with the transformed values.
  * **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 _**Date**_ column with the 4 days as value to get Sub Interval to the given date.

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/O9lRohttBREcA7qfXkMv/image.png" alt=""><figcaption></figcaption></figure>

As a result, a new column gets created with the set sub interval values:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/tj1sQKAfvo50DcZuKrRR/image.png" alt=""><figcaption><p><em><strong>The result column for the Set Sub-interval transform</strong></em></p></figcaption></figure>

## **Subtract Duration** <a href="#subtract-duration" id="subtract-duration"></a>

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:

### Transform Configuration with Other Column <a href="#transform-configuration-with-other-column-1" id="transform-configuration-with-other-column-1"></a>

The _**Subtract Duration**_ transform has been applied to the _**Time Out**_ column, the selected other column is _**Time In**_,

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/MGETHuRHWksFIGmA0ncW/image.png" alt=""><figcaption></figcaption></figure>

As a result, a new column gets added to the Data Grid with the subtracted duration out of the selected columns:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/zND2kc455UsSI4MscNZb/image.png" alt=""><figcaption></figcaption></figure>

### Transform Configuration with Value <a href="#transform-configuration-with-value-1" id="transform-configuration-with-value-1"></a>

The _**Subtract Duration**_ transform has been applied to the _**Time Out\_Time In\_sub\_duration\_1**_, the selected value is 01:03:02,

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/7Il8MaxPQzOLFpqpjiHn/image.png" alt=""><figcaption></figcaption></figure>

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:

<figure><img src="https://content.gitbook.com/content/e7P9Uf1O3iaFlsO3hfWI/blobs/gME4a5ydXwpjVjXGeFHL/image.png" alt=""><figcaption></figcaption></figure>
