Description:

The Transform Time by Adding/Subtracting Time step modifies date and time values by adding or subtracting a configurable amount of time. The transformation is configured via the Configure Transform List property, which defines one or more date/time transformation rules.

Each transformation specifies the source date/time field together with the amount of time to add or subtract. Depending on the configured transformation, the adjustment may be expressed in units such as days, hours, minutes, or seconds.

Unlike formatting operations that only change how a timestamp is displayed, this transformation changes the underlying date/time value itself. The resulting timestamps can then be used by downstream ETL components for scheduling, reporting, deadline calculations, or system integration.

The Transform Time by Adding/Subtracting Time step is commonly used after validating incoming data and before exporting, loading, or performing additional business calculations.

The step supports:

• Date/time arithmetic
• Adding configurable time intervals
• Subtracting configurable time intervals
• Multiple time transformations
• Timestamp normalization
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• SLA deadline calculations
• Due date generation
• Delivery schedule adjustments
• Appointment scheduling
• Time zone offset processing
• Business reporting
• Enterprise system integration

Usage:

The Transform Time by Adding/Subtracting Time step is typically placed after importing and validating date/time values but before reporting, exporting, or performing downstream business calculations.

During execution, each configured transformation adjusts the corresponding date/time value by the specified amount. The transformed values are then passed to the next ETL component.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Adding 30 minutes to appointment times
• Subtracting one day from shipment deadlines
• Calculating invoice due dates
• Extending warranty expiration dates
• Adjusting timestamps to business operating hours
• Creating reminder schedules
• Preparing normalized timestamps for reporting systems

The transformed timestamps can then be consumed by downstream reporting, analytics, scheduling, or integration processes.

Prerequisite:

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

Example:

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

• Create a new ETL definition named “clsTransformTimeDef” and open it in designer mode.
• Drag the “clsInputCSV, clsTransformTime, 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 "clsTransformTime" 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 "clsTransformTime" 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 timespan data type column from the drop-down list. Select the operator to add or subtract from the drop-down list. Select the time format type 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 the Enter key to add. 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 and specify the variable or global reference to hold 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 “clsTransformTime” step should transform time by adding or subtracting as configured for the output file. The adjusted timestamps are now available for downstream reporting, scheduling, or export operations. The variable or global holds the output file’s virtual path, as shown below. 

Tips:

• Validate all date/time values before applying transformations.
• Preserve the original timestamp by duplicating the column first if it is required for auditing or comparison.
• Apply time adjustments before generating reports or calculating deadlines.
• Standardize all source timestamps before combining data from multiple systems.
• Test transformations using representative datasets to verify expected results.
• Document the business rules governing time adjustments to facilitate easier maintenance.
• Combine this transform with Duplicate Column Value, Validate Data, Output Column Transform, Sorting Transform, JSON Output File, or SQL Output components to create comprehensive ETL workflows.

Notes:

• This transformation modifies the actual date/time value rather than its display format.
• Multiple time transformation rules can be configured within a single step.
• Source date/time columns must exist within the ETL schema.
• Both addition and subtraction operations are supported through the configured transformation rules.
• Invalid date/time values or transformation configurations may result in ETL execution errors.
• Processing large datasets with numerous time calculations may increase ETL execution time.
• This transform is commonly used for scheduling, logistics, SLA management, reporting, compliance, data migration, and enterprise integration.
• Verify downstream calculations whenever timestamp adjustments are made.

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 transformation mappings
• Source date/time column selection
• Addition or subtraction values
• 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.