Re: recovery dump on database with different timezone

thanks for your comments, i will change the constraints from date to
timestamp with time zone, i hope this works :)

On Mon, Jan 23, 2017 at 7:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
>

--