Re: pgsql: Implement jsonpath .datetime() method

On Mon, Oct 14, 2019 at 5:36 AM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Sun, Oct 13, 2019 at 5:24 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> writes:
> > > 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.
> >
> > I'd wonder whether this gives the same answers near DST transitions,
> > where it's not real clear which offset applies.
>
> I will try this and share the results.

I've separated refactoring of timestamp to timestamptz cast into a
separate patch. Patchset is attached.

I've investigates the behavior near DST transitions in Moscow
timezone. Last two DST transitions it had in 2010-03-28 and
2010-10-31. It behaves the same with and without patch. The tests
are below.

# set timezone = 'Europe/Moscow';

# select '2010-03-28 01:59:59'::timestamp::timestamptz;
timestamptz
------------------------
2010-03-28 01:59:59+03
(1 row)

# select '2010-03-28 02:00:00'::timestamp::timestamptz;
timestamptz
------------------------
2010-03-28 03:00:00+04
(1 row)

# select '2010-03-28 02:59:59'::timestamp::timestamptz;
timestamptz
------------------------
2010-03-28 03:59:59+04
(1 row)

# select '2010-03-28 03:00:00'::timestamp::timestamptz;
timestamptz
------------------------
2010-03-28 03:00:00+04
(1 row)

# select '2010-10-31 01:59:59'::timestamp::timestamptz;
timestamptz
------------------------
2010-10-31 01:59:59+04
(1 row)

# select '2010-10-31 02:00:00'::timestamp::timestamptz;
timestamptz
------------------------
2010-10-31 02:00:00+03
(1 row)

BTW, I've noticed how ridiculous cast behaves for values in the range
of [2010-03-28 02:00:00, 2010-03-28 03:00:00). Now, I think that
timestamptz type, which explicitly stores timezone offset, has some
point. At least, it would be possible to save the same local time
value during casts.

I'm going to push these two patches if no objections.

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