Re: Implicit timezone conversion replicating from timestamp to timestamptz?

On 24/01/2019 21:57, Jeremy Finzel wrote:
> 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?

This only works by accident in pglogical because the binary
representations of both types are compatible in this sense. You're not
really supposed to do that.

> 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

This is what you get in built-in logical replication, because it goes
via textual representation.

To make it do what you actually wanted, set the time zone for the
subscription worker to UTC. The way to do that (could be easier) is to
create a separate user, use ALTER USER SET timezone = 'UTC', and create
the subscription as that user.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services