How do I use AWS DMS tasks to migrate an Amazon RDS for MySQL database that doesn't use a UTC time zone?

Short description

If your source or target RDS for MySQL DB instance doesn't use a UTC time zone, then your timestamp data can be inconsistent after AWS DMS migration. Internally, RDS for MySQL stores the timestamp column as UTC. When you select a date, RDS for MySQL automatically converts the timestamp column to the current session's time zone.

For example, you have a source RDS for MySQL database that runs in Australia/Sydney and a target RDS for MySQL database that runs in UTC. When the AWS DMS task captures and applies the data as UTC in the source to the target, the data is inconsistent.

To resolve this issue, use the Initstmt=SET time_zone='+00:00'; extra connection attribute (ECA) for the AWS DMS target endpoint. For more information, see Step 6: Create AWS DMS source and target endpoints.

Resolution

Important: Before you migrate data, you must run a CREATE TABLE command for your AWS DMS task. For more information, see CREATE TABLE statement on the MySQL website.

Example CREATE TABLE command:

create table sample_schema.dms_tz_table (
id int not null,
info varchar(30),
ts timestamp null default null,
primary key (id)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

Migration without the Initstmt ECA in the target endpoint

In the following example, the source database is in the Australia/Sydney time zone and the target database is in the America/Caracas time zone. Also, the source database doesn't include the Initstmt ECA in the target endpoint.

To check the source and target database parameters, run the following query:

SELECT @@global.time_zone, @@session.time_zone;

Source database output:

@@global.time_zone|@@session.time_zone|
------------------+-------------------+
Australia/Sydney  |Australia/Sydney   |

Target database output:

@@global.time_zone|@@session.time_zone|
------------------+-------------------+
America/Caracas   |America/Caracas    |

To prepare the data for a full load, run the following commands:

insert into sample_schema.dms_tz_table(id, info, ts) values(1, 'FL', CURRENT_TIMESTAMP);
insert into sample_schema.dms_tz_table(id, info, ts) values(2, 'FL','2025-12-18 19:03:01');

Run your AWS DMS task in full load plus change data capture (CDC) mode. After the full load completes, run the following commands to insert the CDC data into your source database:

insert into sample_schema.dms_tz_table(id, info, ts) values(3, 'CDC', CURRENT_TIMESTAMP);
insert into sample_schema.dms_tz_table(id, info, ts) values(4, 'CDC', '2025-09-01 19:05:01');

To check the data in your source and target databases, run the following SQL query in both the source and target databases:

select t.* from sample_schema.dms_tz_table t;

The following output shows data from the source and target databases:

|Source Australia/Sydney    |     Target America/Caracas|
|-------------------------------------------------------|
|id|info|                 ts|id|info|                 ts|
|--+----+-------------------+--+----+-------------------+
|1 |FL  |2025-12-19 02:12:47|1 |FL  |2025-12-18 15:12:47|
|2 |FL  |2025-12-18 19:03:01|2 |FL  |2025-12-18 08:03:01|
|3 |CDC |2025-12-19 02:17:40|3 |CDC |2025-12-18 11:17:40|
|4 |CDC |2025-09-01 19:05:01|4 |CDC |2025-09-01 05:05:01|

The timestamp data isn't consistent between the source and target databases.

Migration with the Initstmt ECA in the target endpoint

In the following example, the source database is in the Australia/Sydney time zone and the target database is in the America/Caracas time zone. The source database includes the Initstmt=SET time_zone='+00:00'; ECA in the AWS DMS target endpoint

To check the source and target database parameters, run the following query:

SELECT @@global.time_zone, @@session.time_zone;

Source database output:

@@global.time_zone|@@session.time_zone|
------------------+-------------------+
Australia/Sydney  |Australia/Sydney   |

Target database output:

@@global.time_zone|@@session.time_zone|
------------------+-------------------+
America/Caracas   |America/Caracas    |

To prepare the data for a full load, run the following commands:

insert into sample_schema.dms_tz_table(id, info, ts) values(1, 'FL', CURRENT_TIMESTAMP);
insert into sample_schema.dms_tz_table(id, info, ts) values(2, 'FL','2025-09-01 19:03:01');

Run your AWS DMS task in full load plus CDC mode. After the full load completes, run the following commands to insert the CDC data into your source database:

insert into sample_schema.dms_tz_table(id, info, ts) values(3, 'CDC', CURRENT_TIMESTAMP);
insert into sample_schema.dms_tz_table(id, info, ts) values(4, 'CDC', '2025-09-01 19:05:01');

To check the data in your source and target databases, run the following SQL query in both the source and target databases:

select t.* from sample_schema.dms_tz_table t;

The following output shows data from the source and target databases:

|Source Australia/Sydney    |     Target America/Caracas|
|-------------------------------------------------------|
|id|info|                 ts|id|info|                 ts|
|--+----+-------------------+--+----+-------------------+
|1 |FL  |2025-09-03 00:44:31|1 |FL  |2025-09-02 10:44:31|
|2 |FL  |2025-09-01 19:03:01|2 |FL  |2025-09-01 05:03:01|
|3 |CDC |2025-09-03 00:48:15|3 |CDC |2025-09-02 10:48:15|
|4 |CDC |2025-09-01 19:05:01|4 |CDC |2025-09-01 05:05:01|

Migration with the Initstmt ECA in the target endpoint with a target database in the UTC time zone

In the following example, the source database is in the Australia/Sydney time zone and the target database is in the UTC time zone. The source database includes the Initstmt=SET time_zone='+00:00'; ECA in the AWS DMS target endpoint.

To check the source and target database parameters, run the following query:

SELECT @@global.time_zone, @@session.time_zone;

Source database output:

@@global.time_zone|@@session.time_zone|
------------------+------------------+
Australia/Sydney  |Australia/Sydney  |

Target database output:

@@global.time_zone|@@session.time_zone|
------------------+------------------+
UTC               |UTC               |

To prepare the data for a full load, run the following commands:

insert into sample_schema.dms_tz_table(id, info, ts) values(1, 'FL', CURRENT_TIMESTAMP);
insert into sample_schema.dms_tz_table(id, info, ts) values(2, 'FL','2025-09-01 19:03:01');

Run your AWS DMS task in full load plus CDC mode. After the full load completes, run the following commands to insert the CDC data into your source database:

insert into sample_schema.dms_tz_table(id, info, ts) values(3, 'CDC', CURRENT_TIMESTAMP);
insert into sample_schema.dms_tz_table(id, info, ts) values(4, 'CDC', '2025-09-01 19:05:01');

To check the data in your source and target databases, run the following SQL query in both the source and target databases:

select t.* from sample_schema.dms_tz_table t;

The following output shows data from the source and target databases:

|Source Australia/Sydney    |                 Target UTC|
|-------------------------------------------------------|
|id|info|                 ts|id|info|                 ts|
|--+----+-------------------+--+----+-------------------+
|1 |FL  |2025-09-03 01:02:09|1 |FL  |2025-09-02 15:02:09|
|2 |FL  |2025-09-01 19:03:01|2 |FL  |2025-09-01 09:03:01|
|3 |CDC |2025-09-03 01:03:37|3 |CDC |2025-09-02 15:03:37|
|4 |CDC |2025-09-01 19:05:01|4 |CDC |2025-09-01 09:05:01|

Related information

Endpoint settings when using a MySQL-compatible database as a target for AWS DMS