Re: jsonpath: Inconsistency of timestamp_tz() Output

On Jul 2, 2024, at 10:53, David E. Wheeler <david(at)justatheory(dot)com> wrote:

> ``` patch
> --- a/src/test/regress/expected/jsonb_jsonpath.out
> +++ b/src/test/regress/expected/jsonb_jsonpath.out
> @@ -2914,7 +2914,7 @@ HINT: Use *_tz() function for time zone support.
> select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
> jsonb_path_query_tz
> -----------------------------
> - "2023-08-15T07:00:00+00:00"
> + "2023-08-15T00:00:00-07:00"
> (1 row)
>
> select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
> @@ -3003,7 +3003,7 @@ HINT: Use *_tz() function for time zone support.
> select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
> jsonb_path_query_tz
> -----------------------------
> - "2023-08-15T12:34:56+00:00"
> + "2023-08-15T12:34:56+10:00"
> (1 row)
>
> select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
> ```

FWIW I fixed this issue in my jsonpath port, which I released over the weekend.[1] You can see what I think should be the proper output for these two examples in these Playground links, where the response will use your browser’s time zone: [2], [3].

Best,

David

[1]: https://justatheory.com/2024/07/go-sqljson-path/
[2]: https://theory.github.io/sqljson/playground/?p=%2524.timestamp_tz%28%29&j=%25222023-08-15%2522&a=&o=49
[3]: https://theory.github.io/sqljson/playground/?p=%2524.timestamp_tz%28%29&j=%25222023-08-15%252012%253A34%253A56%2522&a=&o=49