On Friday, 25 January 2019 04:57:15 UTC+8, Jeremy Finzel wrote:
>
> We are working to migrate several large tables from the timestamp to the
> timestamptz data type by using logical replication (so as to avoid long
> downtime for type conversions). We are using pglogical but curious if what
> I share below applies to native logical replication as well.
>
> Both source and destination dbs are at localtime, which is
> 'America/Chicago' time zone.
>
> The source system has a timestamp stored "at time zone UTC", like this for
> 6:00pm Chicago time:
> 2019-01-24 20:00:00.000000
>
> I was *very surprised* to find that replicating above timestamp to
> timestamptz actually does so correctly, showing this value in my psql
> client on the subscriber:
> 2019-01-24 14:00:00.000000-06
>
> How does it know/why does it assume it knows that the time zone of the
> timestamp data type is UTC on the provider given that my clusters are at
> America/Chicago? I would have actually expected an incorrect conversion of
> the data unless I set the timezone to UTC on the way in on the subscriber
> via a trigger.
>
> That is, I was expecting to see this:
> 2019-01-24 20:00:00.000000-06
>
> Which is obviously wrong. So why does it do this and is there some
> assumption being made somewhere in the code base that a timestamp is
> actually saved "at time zone UTC"?
>
>
pglogical is replicating the timestamp text, which is converted on both
output and input.