Dates Transforms

The Dates category of transforms allows users to manipulate date and time columns for analysis, calculation, and conversion across time zones.

The Dates transforms provide a comprehensive set of tools for advanced date and time manipulation, allowing users to perform complex operations like calculating time differences, extracting specific temporal components (e.g., day of the week, quarter), reformatting dates for consistency, and accurately converting between various time zones to support sophisticated temporal data analysis and reporting.

Best Situations to Use

• Adjust time values for different time zones.

• Add or calculate durations between date/time columns.

• Standardize datetime formats for reporting and analysis.

• Generate new datetime columns for feature engineering or analytics.

Add Duration

The Add Duration transform adds time to a datetime column. You can add a fixed value or add time from another column.

Best Situations to Use

• Calculate elapsed time between two datetime columns.

• Add a fixed duration to a timestamp (e.g., add 2 hours).

Steps – Using Another Column

1. Select the target column (e.g., Time Out).

2. Open Transforms > Dates > Add Duration.

3. Enable Create New Column (optional).

4. Select Other Column and choose the column providing duration (e.g., Time In).

5. Click Submit.

• A new column shows the summed duration.

Steps – Using a Fixed Value

1. Select the target column.

2. Open Transforms > Dates > Add Duration.

3. Enable Create New Column (optional).

4. Select Value, and enter the fixed duration (e.g., 2 hours).

5. Click Submit.

• A new column is created with the duration added.

Add Interval to Date

Adds a specified interval (years, months, days, weeks, hours, minutes, milliseconds) to a datetime column.

Best Situations to Use

• Shift dates forward or backward for forecasting or scheduling.

• Create derived columns with time-based offsets.

Steps

1. Select a datetime column.

2. Open Transforms > Dates > Add Interval to Date.

3. Enable Create New Column (optional).

4. Configure:

   • Input Format: Year first, Month first, or Day first.

   • Value Type: Interval type (e.g., months).

   • Value: Number of intervals to add.

5. Click Submit.

• A new column is added with the adjusted datetime values.

Note: Supports datetime in yyyy-mm-dd and hh:mm:ss formats.

Convert From UTC

Converts datetime values assumed to be in UTC to a specified time zone.

Best Situations to Use

• Display timestamps in local time zones.

• Align datasets from different regions for analysis.

Steps

1. Select a UTC datetime column.

2. Open Transforms > Dates > Convert From UTC.

3. Provide a new column name.

4. Select the Target Time Zone.

5. Click Submit.

• A new column contains the datetime values in the target time zone.

Note: Inputs with time zone offsets are invalid.

Convert Timezone

Converts datetime values from a source time zone to a target time zone.

Best Situations to Use

• Normalize timestamps from different sources or time zones.

• Prepare datasets for time-sensitive analytics.

Steps

1. Select a datetime column.

2. Open Transforms > Dates > Convert Timezone.

3. Provide a new column name.

4. Specify Source Time Zone and Target Time Zone.

5. Click Submit.

• A new column with converted datetime values.

Note: Inputs with time zone offsets are invalid.

Convert To UTC

Converts datetime values from a specified time zone to UTC.

Best Situations to Use

• Standardize timestamps for data integration.

• Ensure consistent time references across global datasets.

Steps

1. Select a datetime column.

2. Open Transforms > Dates > Convert To UTC.

3. Provide a new column name.

4. Specify the Source Time Zone.

5. Click Submit.

• A new column is created containing datetime values in UTC.

Note: Inputs with time zone offsets are invalid.

Extract Date Part

Extracts specific components from a date or datetime column, such as day, month, year, week, quarter, or day of the week/year.

Best Situations to Use

• Derive features like month, quarter, or day of week for aggregation.

• Analyze temporal patterns in datasets.

Steps

1. Select a datetime column.

2. Open Transforms > Dates > Extract Date Part.

3. Select the Input Format for the column.

4. Choose the components to extract:

   • Day, Month, Year

   • Day of Week (1–7, Sun–Sat, Sunday–Saturday)

   • Day of Year (1–365)

   • Week of Year (1–53)

   • Quarter (Normal, Financial, Custom)

5. Click Submit.

• New columns are created for each selected date part.

Note: Supports datetime format yyyy-mm-dd hh:mm:ss.

Extract Time Unit

Extracts hours, minutes, seconds, milliseconds, or converts time to milliseconds from a time or datetime column.

Best Situations to Use

• Analyze time-based patterns within a day.

• Prepare features for temporal analysis or modeling.

Steps

1. Select a datetime column.

2. Open Transforms > Dates > Extract Time Unit.

3. Select units to extract: Hours, Minutes, Seconds, Milliseconds, Time to Milliseconds.

4. Click Submit.

• New columns are added for the selected time units.

Note: Supports time formats hh:mm:ss:fff, hh:mm:ss, hh:mm.

Find Date Difference

Calculates the difference between two dates or a date and a fixed value.

Best Situations to Use

• Compute duration or age between dates.

• Derive feature columns for temporal analysis.

Steps

1. Select a date column.

2. Open Transforms > Dates > Find Date Difference.

3. Enable Create New Column (optional).

4. Specify:

   • Input Format

   • Use With: Value or another column

   • Value: Date or reference column

5. Click Submit.

• A new column is added with the calculated date differences.

Format Date

Changes the format of a date column according to desired patterns for year, month, day, delimiter, or timestamp inclusion.

Best Situations to Use

• Standardize date columns for reporting or analysis.

• Convert date formats to match system or application requirements.

Steps

1. Select a date column.

2. Open Transforms > Dates > Format Date.

3. Configure:

   • Source Format Hint

   • Target Format (Year, Month, Day)

   • Year Pattern: yyyy or yy

   • Month Pattern: 0-12, Jan-Dec, January-December

   • Delimiter

   • Include Timestamp (optional)

4. Click Submit.

• The date column is reformatted as specified.

From Unix Time

Converts Unix timestamps to human-readable date formats.

Best Situations to Use

• Convert datasets with epoch time to a readable datetime.

• Prepare data for analysis or visualization.

Steps

1. Select a Unix timestamp column.

2. Open Transforms > Dates > From Unix Time.

3. Enable Create New Column (optional).

4. Specify Output Format.

5. Click Submit.

• A new column is added with converted datetime values.

Sub Interval to Date

Subtracts a specified time interval (years, months, days, weeks, etc.) from a date column.

Best Situations to Use

• Compute historical dates relative to a reference date.

• Adjust dates for scheduling, forecasting, or backdating analysis.

Steps

1. Select a date column.

2. Open Transforms > Dates > Sub Interval to Date.

3. Enable Create New Column (optional).

4. Configure:

   • Input Format

   • Value Type: Interval type (years, months, days, weeks)

   • Value: Number of units to subtract

5. Click Submit.

• A new column has been added with adjusted dates.

Dates Transforms – Subtract Duration

The Subtract Duration transform allows users to deduct time values in a column either by a fixed value or by another column’s time values. It supports durations in formats: hh:mm:ss.mmm, hh:mm:ss, and hh:mm. The result can replace the original column or be stored in a new column.

Best Situations to Use

• Calculate time differences between start and end times.

• Deduct fixed durations for time adjustments in schedules or logs.

• Generate derived columns for analysis involving time intervals.

Not Recommended for:

• Non-time columns.

• Complex durations requiring multiple-step calculations across datasets (consider using Expression Editor).

Steps to Perform Subtract Duration

1. Using Another Column

1. Select a column containing time values (e.g., Time Out).

2. Navigate to Transforms > Dates > Subtract Duration.

3. Enable Create New Column (optional).

4. Select Use With > Other Column, and choose the column providing time values to subtract (e.g., Time In).

5. Click Submit.

• A new column is added showing the difference between the selected columns.

• Example: Time Out – Time In → Subtracted Duration.

2. Using a Fixed Value

1. Select a column containing time values.

2. Navigate to Transforms > Dates > Subtract Duration.

3. Enable Create New Column (optional).

4. Select Use With > Value, and enter a fixed time value (e.g., 01:03:02).

5. Click Submit.

• A new column is added showing the remaining values after subtracting the fixed duration from the selected column.

• Example: Time Out – 01:03:02 → Adjusted Duration.

Notes:

• Supports time formats: hh:mm:ss.mmm, hh:mm:ss, and hh:mm.

• Ensure columns contain valid time values before applying the transform.

• Use Create New Column to preserve original data for reference.