On Fri, Mar 1, 2019 at 3:36 AM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote:
> I can also offset to explicitly pass timezone info into jsonpath function using
> the special user dataype encapsulating struct pg_tz.
More interesting question is what would be the source of timezone. If
even you encapsulate timezone in a separate datatype, the expression
will be still just stable assuming timezone is generated by stable
subexpression. What we actually need is immutable timezone. Day once
timezone is updated, you create new timezone version, while old
version is immutable. Then if jsonpath has given particular *timezone
version*, it might remain immutable. But that requires significant
rework of our timezone infrastructure.
> But simple integer timezone offset can be passed now using jsonpath variables
> (standard says only about integer timezone offsets; also it requires presence
> of timezone offset it in the input string if the format string contain timezone
> components):
>
> =# SELECT jsonb_path_query(
> '"28-02-2019 12:34"',
> '$.datetime("DD-MM-YYYY HH24:MI TZH", $tz)',
> jsonb_build_object('tz', EXTRACT(TIMEZONE FROM now()))
> );
>
> jsonb_path_query
> -----------------------------
> "2019-02-28T12:34:00+03:00"
> (1 row)
Standard specifies fixed offset to be given for *particular datetime*.
For instance, if json contains offset in separate attribute or
whatever, then it's OK to use such two-arguments .datetime() method.
But that seems quite narrow use case.
Standard doesn't mean you get fixed offset extracted from "now()" and
apply it to random datetimes in your json collection. That would work
correctly for real timezones only when they are fixed offsets, but
there are almost none of them! So, that's just plain wrong, we never
should encourage users to do something like this.
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company