Re: [GENERAL] recovery dump on database with different timezone - Mailing list pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 01/23/2017 05:14 PM, David G. Johnston wrote:
>> To your example - testing in UTC is going to always result in failure
>> for Z values <= 0 since they will all result in a UTC date of
>> 2011-01-01.  Choosing +06 would result in a passed test.

> That was sort of the point, I was just using the value that the OP said
> worked:
> "if change 2011-01-01 00:00:03.925+00 to 2011-01-01 00:00:03.925-06
> works ok"
> I could not see how it did.

Well,

select '2011-01-01 00:00:03.925-06'::timestamptz >= '2011-01-01'::date;

passes if TimeZone is US central time (UTC-6) or anyplace east of there.
It fails west of there, because the "date" value is interpreted as
midnight local time for purposes of comparison to a "timestamptz" value:

regression=# set timezone = EST5EDT;
SET
regression=# select '2011-01-01 00:00:03.925-06'::timestamptz >= '2011-01-01'::date;
 ?column?
----------
 t
(1 row)

regression=# set timezone = PST8PDT;
SET
regression=# select '2011-01-01 00:00:03.925-06'::timestamptz >= '2011-01-01'::date;
 ?column?
----------
 f
(1 row)

The key point here is that a CHECK constraint is checked when the row
is stored, and if it depends on any GUC parameters then the
then-prevailing parameter will be used.  So the OP's problem is he has
some rows that passed the constraint based on the TimeZone value that
was active when they were stored, but they don't pass the constraint
when TimeZone is UTC.

If the failing rows are failing because of this side of the range
constraint, they must have been stored under a zone setting east
of UTC.  But it's just as likely that they are failing because of
the other side of the range constraint (the <= 2012-01-01 end),
implying that they were stored under a zone setting west of UTC.

                        regards, tom lane