Issue with AWS DMS time_zone on timestamps column

Question

My Current Source DB is non-UTC timezone (Asia/Calcutta, UTC+5:30).

I use AWS DMS to move certain tables from SourceDB to a TargetDB which is set to same non-UTC time_zone (Asia/Calcutta) in Parameter Group.

When I create and run a "Migrate existing data and replicate ongoing changes", data gets migrated but the timestamp column is different value (actually it is UTC)

table1 @ Source DB
----------------------------------------------------
id    user_id  purpose           in_time
1     24       Cleaning          2020-08-09 20:00:00
2     78       Cleaning          2020-08-09 21:30:00

table1 @ Target DB
----------------------------------------------------
id    user_id  purpose           in_time
1     24       Cleaning          2020-08-09 14:30:00
2     78       Cleaning          2020-08-09 16:00:00

Now, I stop the DMS task and then resume it again. (This step it important else, following will not be same as described).

After this, when I update row 1 say purpose column on source DB replication happens and suddenly time appears with Asia/Calcutta time_zone on target DB, but only for that ROW 1, ROW 2 in time is still UTC.

table1 @ Source DB
----------------------------------------------------
id    user_id  purpose           in_time
1     24       Survey            2020-08-09 20:00:00
2     78       Cleaning          2020-08-09 21:30:00

table1 @ Target DB
----------------------------------------------------
id    user_id  purpose           in_time
1     24       Survey            2020-08-09 20:00:00
2     78       Cleaning          2020-08-09 16:00:00

Any clue why it behaves like this, is there any way to fix this?

P.S. I am not looking for answers suggesting "You should always use UTC format in you DB", as that isn't an option for me.

karmendra · Accepted Answer · 2020-08-19T18:50:04.147

Ok, I finally was able to fix this.

The answer is in this aws document, but not very obvious. Look at Extra connection attributes when using MySQL as a source for AWS DMS

We need to define serverTimezone in Extra Connection Attributes while defining Endpoint when your source DB is non-UTC timezone.

Specifies the time zone for the source MySQL database.

Example: serverTimezone=US/Pacific;

Note: Do not enclose time zones in single quotes.

Also note that there is similar setting for your Target Endpoint if it is in non-UTC timezone, see this aws documentation, initstmt=SET time_zone='US/Pacific' for setting time_zone of target DB if it is non-UTC (note that time_zone is in single quotes here).

Specifies the time zone for the target MySQL-compatible database.

Default value: UTC

Valid values: A three- or four-character abbreviation for the time zone that you want to use. Valid values are the standard time zone abbreviations for the operating system hosting the target MySQL-compatible database.

Example: initstmt=SET time_zone=UTC

One very important thing to note is when you are setting the Target DB time_zone, accepted time_zone value is as per the OS i.e. Linux. and not the DB i.e. MySql.

In my case following values were used:

source DB serverTimezone=Asia/Calcutta
target DBinitstmt=SET time_zone='Asia/Kolkata'

You need to set both values as per your DB time_zone apart from setting the time_zone in DB Parameter Group.

Issue with AWS DMS time_zone on timestamps column

1 Answers1