Thread: Implicit timezone conversion replicating from timestamp to timestamptz?
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
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"?
Thanks,
Jeremy
Re: Implicit timezone conversion replicating from timestamp totimestamptz?
From
Peter Eisentraut
Date:
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
Re: Implicit timezone conversion replicating from timestamp totimestamptz?
From
"craig.ringer"
Date:
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.000000I 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-06How 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-06Which 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.