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 <t...@sss.pgh.pa.us> wrote: > Adrian Klaver <adrian.kla...@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 > --
