How to use DMS transformation rules to migrate Oracle’s timestamp without timezone column to PostgreSQL

Customers while doing heterogenous migrations might require the migration of data from databases with different time zones. The timestamp column in Oracle does not include the timezone. By default, DMS will migrate it to the data type "timestamp without time zone" in Postgres without making the time-zone changes, leading to incorrect data. In order to fix such scenarios, we can use datetime function in the DMS transformation rule to do the conversion.

I created a RDS Oracle 19c source database with "America/New_York" time zone and Aurora Postgres 16.6 in UTC timezone.

Oracle database timezone:

SELECT systimestamp, SESSIONTIMEZONE FROM DUAL

26-MAY-25 05.02.01.862771000 PM -04:00 America/New_York

PostgreSQL database timezone:

postgres=> show timezone;

TimeZone

----------

UTC

(1 row)

1. Create the following table in the source database Oracle:

CREATE TABLE time_demo_test (
    id NUMBER PRIMARY KEY,
    plain_ts TIMESTAMP
);

2. Insert some data:

INSERT INTO time_demo_test VALUES (
    1,
    CURRENT_TIMESTAMP
);

INSERT INTO time_demo_test VALUES (
    2,
    TIMESTAMP '2024-04-30 10:30:00'
);

INSERT INTO time_demo_test VALUES (
    3,
    CURRENT_TIMESTAMP(0)
);

3. Query the data

select * from time_demo_test;

4. Use DMS to migrate this table.

As shown in the screenshot, the plain_ts column in PostgreSQL displays timestamps in EST format (matching the source database's timezone). By default, Aurora PostgreSQL operates in UTC timezone. When running Database Migration Service (DMS) without any transformations, timestamp without a timezone column are migrated exactly as they appear in the source, without timezone conversion. To ensure correct timezone representation, consider the following two workarounds.

Workarounds

1. Make changes at the target directly by adding an additional column and copy data from existing column

ALTER TABLE time_demo_test
ADD COLUMN timestamp_column_utc TIMESTAMP WITH TIME ZONE;

UPDATE time_demo_test
SET timestamp_column_utc = plain_ts AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC';

2. Use DMS Transformation rule. This will also add a new column with the converted data. In this case, you allow DMS to create the target table table structure for you. Make sure the table structure does not exist on the target.

{
    "rules": [
        {
            "rule-type": "transformation",
            "rule-id": "835493291",
            "rule-name": "835493291",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "%",
                "table-name": "%"
            },
            "rule-action": "add-column",
            "value": "time_in_utc",
            "expression": "DATETIME($PLAIN_TS, '04:00')",
            "data-type": {
                "type": "datetime",
                "precision": 10,
                "scale": 6
            }
        },
        {
            "rule-type": "transformation",
            "rule-id": "558232268",
            "rule-name": "558232268",
            "rule-target": "column",
            "object-locator": {
                "schema-name": "VANSHIKA",
                "table-name": "TIME_DEMO_TEST",
                "column-name": "%"
            },
            "rule-action": "convert-lowercase",
            "value": null,
            "old-value": null
        },
        {
            "rule-type": "transformation",
            "rule-id": "558209025",
            "rule-name": "558209025",
            "rule-target": "table",
            "object-locator": {
                "schema-name": "VANSHIKA",
                "table-name": "TIME_DEMO_TEST"
            },
            "rule-action": "convert-lowercase",
            "value": null,
            "old-value": null
        },
        {
            "rule-type": "transformation",
            "rule-id": "558187775",
            "rule-name": "558187775",
            "rule-target": "schema",
            "object-locator": {
                "schema-name": "VANSHIKA"
            },
            "rule-action": "convert-lowercase",
            "value": null,
            "old-value": null
        },
        {
            "rule-type": "selection",
            "rule-id": "558154712",
            "rule-name": "558154712",
            "object-locator": {
                "schema-name": "VANSHIKA",
                "table-name": "TIME_DEMO_TEST"
            },
            "rule-action": "include",
            "filters": []
        }
    ]
}

Please note in both of the workaround you can remove the old column and rename the new column once the migration is complete.

Thus these workarounds ensures that timestamps are properly represented in UTC timezone in the target PostgreSQL database.