Re: pgsql: Implement jsonpath .datetime() method

On Thu, Oct 3, 2019 at 4:48 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Oct 1, 2019 at 1:41 PM Alexander Korotkov
> <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> > So, basically standard requires us to suppress any error happening in
> > filter expression.
>
> Sounds like the standard is dumb, then. :-)
>
> > But as I wrote before suppression of errors in
> > datetime comparison may lead to surprising results. That happens in
> > rare corner cases, but still. This makes uneasy choice between
> > consistent behavior and standard behavior.
>
> Yeah.

Proposed patch eliminates this dilemma in particular case. It
provides correct cross-type comparison of datetime values even if one
of values overflows during cast. In order to do this, I made cast
functions to report whether lower or upper boundary is overflowed. We
know that overflowed value is lower (or upper) than any valid value
except infinity.

This patch also changes the way timestamp to timestamptz cast works.
Previously it did timestamp2tm() then tm2timestamp(). Instead, after
timestamp2tm() it calculates timezone offset and applies it to
original timestamp value. I hope this is correct. If so, besides
making overflow handling easier, this refactoring saves some CPU
cycles.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company