Implicit timezone conversion replicating from timestamp to timestamptz?

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: pglogical-list(at)2ndquadrant(dot)com, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Implicit timezone conversion replicating from timestamp to timestamptz?
Date: 2019-01-24 20:57:02
Message-ID: CAMa1XUiLU82JYgO8uqphDkBJbsZZra6S_B_QSrqQmH8a4+jQMg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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"?

Thanks,
Jeremy

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-01-24 21:17:12 Re: Delay locking partitions during INSERT and UPDATE
Previous Message John Naylor 2019-01-24 20:50:17 Re: Delay locking partitions during INSERT and UPDATE