Handle timezones in datetime operations in Jitterbit Studio

Introduction

Source systems store timestamps in their own local timezone. Salesforce records timestamps in the running user's timezone; NetSuite uses the company's timezone setting. When timestamps from these systems flow into a cross-region integration without conversion, silent errors result: records appear to be created hours in the past or future, comparison-based deduplication fails, and scheduled triggers fire at unexpected times.

This guide covers three date and time functions that work together to handle this:

• GetUTCFormattedDateTime: converts a local timestamp to a UTC ISO 8601 string, suitable for storage.
• ConvertTimeZone: shifts a timestamp from one timezone to another, for delivery or display.
• FormatDate: formats a converted timestamp as a readable string.

Two patterns are covered:

• Part 1: Normalize incoming timestamps to UTC: Convert local timestamps from the source system to UTC on ingest, so all stored values are timezone-neutral.
• Part 2: Convert UTC to a local timezone for display: Shift stored UTC timestamps to the recipient's local timezone and format them for output.

Design pattern

All three functions run inside transformation field mapping scripts. The operation structure is the same for both patterns:

The timezone logic lives in the field mapping script for the timestamp field on the target side. No separate operation step is needed for timezone conversion.

Part 1: Normalize incoming timestamps to UTC

Use GetUTCFormattedDateTime to convert a local timestamp from the source system into a UTC ISO 8601 string. Storing UTC eliminates timezone ambiguity and allows safe comparisons across records from different systems.

Step 1: Identify the source timezone

Determine which timezone the source system uses for timestamps. Common examples:

• Salesforce: Timestamps use the timezone of the user whose credentials run the integration. Check the user's timezone under Setup > Manage Users > Users.
• NetSuite: Timestamps use the company's default timezone. Check this under Setup > Company > Company Information.

Use a full IANA TZ code when specifying the timezone (for example, "America/Los_Angeles" or "Europe/London"). Abbreviations such as "PST" are supported but not recommended: "AST" is ambiguous between Atlantic Standard Time and Arabia Standard Time.

A full list of IANA TZ codes is available at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.

Step 2: Write the mapping script

In the transformation, open the field mapping for the timestamp field on the target side. Write a script that calls GetUTCFormattedDateTime with the source field and the source timezone:

GetUTCFormattedDateTime(Source.created_at, "America/Los_Angeles");
// If Source.created_at is "2024-03-15 09:00:00"
// Returns "2024-03-15T17:00:00Z"

The return value is always in ISO 8601 format (yyyy-mm-ddTHH:MM:SSZ). Most systems accept ISO 8601 as a standard timestamp format.

Step 3: Handle European date format (optional)

If the source system formats dates as day/month/year rather than month/day/year, set the optional is_european_format argument to true:

GetUTCFormattedDateTime(Source.created_at, "Europe/London", true);
// Parses "15/03/2024 09:00:00" as 15 March 2024 at 09:00
// Returns "2024-03-15T09:00:00Z"

Part 2: Convert a UTC timestamp to a local timezone for display

After storing UTC timestamps, convert them to the recipient's local timezone when sending data to a target system or generating a notification. Use ConvertTimeZone to shift the time, then FormatDate to produce a readable string.

Step 1: Convert to the target timezone

Use ConvertTimeZone to shift the UTC timestamp to the target timezone. Assign the result to a local variable:

localTime = ConvertTimeZone(Source.updated_at, "UTC", "America/New_York");
// If Source.updated_at is "2024-03-15T17:00:00Z"
// Returns "2024-03-15 13:00:00"

By default, ConvertTimeZone accounts for daylight saving time when converting between the four major US timezones. To disable this behavior, pass true as the optional ignoreDST argument.

Step 2: Format the converted timestamp

Use FormatDate to control how the timestamp appears in the target field. The format argument uses placeholder characters for year (yyyy), month (mm), day (dd), hour (HH), minute (MM), and second (SS). strftime-style codes (for example, %B for full month name) are also supported.

Common formats:

Step 3: Combine into a single mapping script

In the field mapping script for the target display field, chain both calls. Assign the ConvertTimeZone result to a variable, then return the FormatDate of that variable:

localTime = ConvertTimeZone(Source.updated_at, "UTC", "America/New_York");
FormatDate(localTime, "yyyy-mm-dd HH:MM:SS");

The last expression in a mapping script is its return value, so FormatDate provides the value written to the target field.

Verify the integration

Verifying Part 1 (normalize to UTC)

1. In the transformation, add the GetUTCFormattedDateTime mapping script to a target timestamp field.

2. Choose a source record with a known local timestamp (for example, a record created at 9:00 AM Pacific time).

3. Deploy and run the operation.

4. In the operation log or in the target system, confirm that the stored timestamp matches the expected UTC equivalent (in this example, 2024-03-15T17:00:00Z for a Pacific Standard Time offset of −8).

5. Test with a timestamp near a daylight saving time transition in the source timezone (for example, a record created at 1:30 AM Pacific time on the day clocks spring forward). Confirm the UTC offset shifts correctly.

6. If the output timestamp is identical to the input, confirm that the time_zone_id argument is a valid IANA code with correct capitalization. An unrecognized timezone code causes GetUTCFormattedDateTime to return the input unchanged.

Verifying Part 2 (convert UTC for display)

1. Add the ConvertTimeZone and FormatDate mapping script to a target display field.

2. Use a source record that contains a known UTC timestamp (for example, "2024-03-15T17:00:00Z").

3. Deploy and run the operation.

4. In the target system, confirm that the display field shows the expected local time for the target timezone (in this example, 2024-03-15 13:00:00 for Eastern time).

5. Test with a UTC timestamp that falls near a daylight saving time transition in the target timezone. Confirm that the offset adjusts correctly.

6. If the formatted output is unexpected, verify the format string argument to FormatDate. Characters that are not recognized as format placeholders are written to the output as-is, which can produce unexpected results when a format string contains an unintended token.