On 01/24/2017 06:58 AM, Edmundo Robles wrote:
> thanks for your comments, i will change the constraints from date to
> timestamp with time zone, i hope this works :)
There is good chance it will not work until the data is cleaned up or
unless you pick just the right timezone to use in the constraint. Tom's
point being that it looks like the data was entered under different
timezones over the life of the database. It comes down to what you 'day'
range you want the CHECK to work on. In other words what timezone do you
want to anchor the range to?
>
> On Mon, Jan 23, 2017 at 7:48 PM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Adrian Klaver <adrian.klaver@aklaver.com
> <mailto: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
>
>
>
>
> --
>
--
Adrian Klaver
adrian.klaver@aklaver.com