On Mon, Oct 14, 2019 at 5:36 AM Alexander Korotkov
wrote:
> On Sun, Oct 13, 2019 at 5:24 AM Tom Lane wrote:
> > Alexander Korotkov 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
0001-Refactor-timestamp2timestamptz_opt_error-3.patch
Description: Binary data
0002-Refactor-jsonpath-s-compareDatetime-3.patch
Description: Binary data
