On Wed, Jul 3, 2024 at 12:51 AM David E. Wheeler <david@justatheory.com> wrote:
>
> Hackers,
>
> In fuzing around trying to work out what’s going on with the formatting of timestamptz values cast by the
timestamp_tz()jsonpath method[1], I noticed that the formatting of the string() method applied to date and time objects
wasnot fully tested, or how the output is determined by the DateStyle method.
>
> The attached path aims to rectify this situation by adding tests that chain string() after the jsonpath date/time
methods,both with the default testing “PostreSQL” DateStyle and “ISO”. It also mentions the impact of the DateStyle
parameterin the string() documentation.
>
> Also available to review as a pull request[2].
>
> Best,
>
> David
>
> [1]: https://www.postgresql.org/message-id/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.com
> [2]: https://github.com/theory/postgres/pull/7/files
>
>
+set datestyle = 'ISO';
+select jsonb_path_query_tz('"2023-08-15 12:34:56"',
'$.timestamp_tz().string()'); -- should work
+ jsonb_path_query_tz
+--------------------------
+ "2023-08-15 12:34:56-07"
+(1 row)
Do you need to reset the datestyle?
also the above query is time zone sensitive, maybe the time zone is
set in another place, but that's not explicit?
<para>
- String value converted from a JSON boolean, number, string, or datetime
+ String value converted from a JSON boolean, number, string, or
+ datetime. Note that the string output of datetimes is determined by
+ the <xref linkend="guc-datestyle"/> parameter.
</para>
imho, your patch has just too many examples.
for explaining the above sentence, the following example should be enough.
begin;
set local time zone +1;
set local datestyle to postgres;
select jsonb_path_query_tz('"2023-08-15 12:34:56"',
'$.timestamp_tz().string()');
set local datestyle to iso;
select jsonb_path_query_tz('"2023-08-15 12:34:56"',
'$.timestamp_tz().string()');
commit;
