Why doesn't AWS DMS successfully migrate GMT/BST timestamps from and to RDS PostgreSQL databases?

Question

I'm using AWS DMS 3.4.6 to migrate a PostgreSQL 13.6 database to another PostgreSQL 13.6 database, both in AWS RDS.

One particular column in my database is a timestamp with time zone.

The 'timezone' property in the parameter groups for each database is set to 'GB'.

In my source database, I have the following value in my column:

2016-11-08 09:44:49.704142+00

This is migrated to the target database as:

2016-11-08 10:44:49.704142+00

The hour value is 1 hour greater than it should be.

In fact, this happens for all timestamps that would actually be GMT as opposed to BST. All BST timestamps migrate successfully.

What's happening?

Answer 1

After some reading of the AWS DMS docs, it became clear that migration of the timestamp-with-time-zone type for PostgreSQL isn't fully supported (although it's not clear precisely what should work and what shouldn't).

As I know that PostgreSQL stores these types as UTC, I decided to try setting the target database 'timezone' property (in the parameter group for the AWS RDS PostgreSQL db) to 'UTC' instead of 'GB'. This resulted in a correct migration. After completing the migration I changed the 'timezone' property for the target to be 'GB'. This now resulted in the stored 'UTC' values correctly displaying on selection for both GMT and BST date-time values.