Challenge
We are using AWS DMS to convert data from IBM DB2 into AWS Auroa Postgres
IBM DB2 server is set to Eastern time (ET) zone. Timestamp related data inside DB2 is therefore in ET.
Findings
AWS DMS basically functions by making a connection to the source (DB2) and the destination (PG) and copying the data from one to the other.
DMS is connecting to PG and appears to be setting the connection time zone to UTC. We have tried changing the PG server config time zone to America/New_York to try and get the DMS connection to default to that, but it won't - we surmise that DMS is specifically setting its connection to UTC.
We are fairly certain that if we could get DMS to connect as ET that this would all work perfectly, however we can't seem to find a way to do that.
Other Ideas
Is there some server configuration that might be able to force a client to use a certain session time zone?
We are trying to avoid post-load conversion of the data due to time constraints.
We were able to get this working by have PG convert the data as part of a without time zone to with time zone ALTER as mentioned in this related question - but that is a post-load conversion which we are trying to avoid.
AWS DMS transformations are being investigated but we haven't figured out a solution using them yet.
AWS DMS allows setting connection options for MySQL destinations, but not PG 😔
Any ideas on this would be appreciated.
