Description:

The Transform Time by Time Difference step modifies one or more date/time values by applying a configurable time difference. The transformation is configured through the Configure Time Difference property, where ETL designers specify the source column, destination column (if applicable), and the time interval to apply.

This transformation is commonly used when timestamps must be shifted to account for business requirements such as time zone adjustments, scheduling offsets, processing windows, reporting cut-off times, or system-specific date calculations.

Unlike formatting transforms that change only the appearance of a date or time, this step modifies the underlying date/time value by calculating a new value based on the configured difference.

The Transform Time by Time Difference step is typically placed after data validation and before reporting, exporting, or additional time-based calculations.

The step supports:

• Date and time adjustments
• Configurable time differences
• Time offset calculations
• Timestamp normalization
• Scheduling support
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Time zone conversion
• Business hour adjustments
• SLA deadline calculations
• Processing window alignment
• Event scheduling
• Reporting normalization
• Enterprise system integration

Usage:

The Transform Time by Time Difference step is typically placed after importing and validating date/time data, but before exporting or performing downstream calculations that depend on adjusted timestamps.

During execution, each configured date/time value is processed independently. The specified time difference is applied, and the resulting value becomes available to subsequent ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Adjusting timestamps from UTC to local business time
• Adding processing delays to scheduled events
• Calculating service-level agreement (SLA) deadlines
• Offsetting manufacturing or logistics schedules
• Normalizing timestamps from multiple source systems
• Preparing reporting periods based on business calendars
• Aligning timestamps before data warehouse loading

The transformed date/time values can then be used by downstream reporting, analytics, or integration processes.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsTransformTimeDifferenceDef” example.          

• Create a new ETL definition named “clsTransformTimeDifferenceDef” and open it in designer mode.
• Drag the “clsInputCSV, clsTransformTimeDifference, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global to hold the virtual path.
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsTransformTimeDifference" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsTransformTimeDifference" step to configure its "Required" properties. Click the button to configure the transform list. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the start timespan data type column from the drop-down list. Select the end timespan data type column from the drop-down list. Select the time format (hours, minutes, seconds) from the drop-down list for transformation. Select the output column from the drop-down list. You may also type a new Out column name and press Enter to add it. Click the Save button. You may add multiple columns for transformation using the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformTimeDifference” step should apply the configured time difference to the output file. The adjusted timestamps are then available for downstream reporting, scheduling, or export operations. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Validate incoming date/time values before applying time differences.
• Standardize source time zones before performing adjustments across multiple systems.
• Apply time offsets before generating reports or calculating deadlines.
• Verify the resulting timestamps when processing daylight saving or regional time zone changes.
• Use meaningful destination column names if preserving the original timestamp is required.
• Test time calculations using representative datasets before deploying to production.
• Combine this transform with Validate Data, Duplicate Column Value, Set Field Value, Output Column Transform, Sorting Transform, JSON Output File, or SQL Output components to build comprehensive ETL workflows.

Notes:

• This transformation modifies the underlying date/time value rather than its display format.
• Multiple time difference mappings can be configured within a single transformation.
• Source date/time columns must exist within the ETL schema.
• The configured time difference may be positive or negative, depending on the desired adjustment.
• Invalid date/time values or configuration errors may result in ETL execution errors.
• Large datasets containing extensive date/time calculations may increase ETL processing time.
• This transform is commonly used for scheduling, reporting, logistics, compliance, data migration, business intelligence, and enterprise integration.
• Review downstream processes whenever timestamp calculations are modified.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Time difference mappings
• Source date/time column selection
• ETL schema validation
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.