Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
