1

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?

Ermiya Eskandary
  • 15,323
  • 3
  • 31
  • 44
maxedout
  • 21
  • 3
  • Interesting! It might be that DMS does not successfully pick up on the fact that BST might not be applied depending on the actual date. Why not remove the timezone property and let it move everything as is? Or are you using DMS to `fix` the timezones? – Ermiya Eskandary May 07 '22 at 13:39
  • Thanks Ermiya, I appreciate your response. Removing the timezone property isn't really an option for us. However, you prompted me to think that changing my target db timezone to 'UTC' rather than having it set to be 'GB', the same as the source db, may be the way to go (as PostgreSQL stores the timestamp-with-time-zone type as UTC anyway). I tried this out and migration worked ok. – maxedout May 09 '22 at 10:38
  • Perfect - please do add an answer as you have a great question :) – Ermiya Eskandary May 09 '22 at 10:40

1 Answers1

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.

maxedout
  • 21
  • 3