IBM DB2 server is set to
Eastern time (ET) zone. Timestamp related data inside DB2 is therefore in ET.
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.
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.
Any ideas on this would be appreciated.